﻿using Nlnet.Avalonia.Controls;
using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;
using 畜牧业审核程序国产.Models.ToolsClass;
using static Microsoft.EntityFrameworkCore.DbLoggerCategory;

namespace 畜牧业审核程序国产.Models
{
    internal class ShowA406Msg
    {
        private string[] ArrStrIndicatorOfA406 = new string[]
             {
                    "存栏头数（含未断奶小猪）01",
                    "能繁殖的母猪02",
                    "生猪出栏头数03",
                    "牛存栏头数04",
                    "良种及改良种乳牛05",
                    "牛出栏头数06",
                    "羊存栏只数07",
                    "羊出栏只数08",
                    "家禽存栏只数09",
                    "家禽出栏只数10",
                    "兔存栏只数11",
                    "兔出栏只数12",
                    "肉类产量13",
                    "猪  肉14",
                    "牛  肉15",
                    "羊  肉16",
                    "禽  肉17",
                    "兔  肉18",
                    "其  它19",
                    "鸡鸭鹅蛋产量20",
                    "生牛奶产量21",
                    "蚕茧产量22",
                    "鸡存栏23",
                    "鸡出栏24"
             };
        private Dictionary<string, ValueTuple<string, string>> DicA406表对应关系 = new Dictionary<string, ValueTuple<string, string>>
        {
            { "生猪存栏头数（含未断奶小猪）01", ("期末存栏合计01", "A209")},{ "能繁殖的母猪存栏头数02",("能繁殖母猪存栏数06", "A209") },{ "生猪出栏头数03",("当季出栏数", "A209") },
            { "牛存栏头数04",("期末存栏头数01", "A210")},{"良种及改良种乳牛存栏头数05",("奶牛存栏数04","A210") },{ "牛出栏头数06",("当季出栏数","A210")},{ "生牛奶产量（吨）21",("当季生牛奶产量（公斤）18","A210")},
            {"羊存栏只数07",("期末存栏只数01","A211") },{"羊出栏只数08",("当季出栏数","A211") },
            { "家禽存栏只数09",("期末存栏只数01","A212")},{"家禽出栏只数10",("当季家禽出栏数","A212") },{ "鸡鸭鹅蛋产量（吨）20",("当季禽蛋产量（公斤）15","A212")},{ "鸡存栏只数23",("当季鸡存栏数","A212")},{ "鸡出栏只数24",("当季鸡出栏数","A212")}
        };
        //private Tuple<string, string, string> tupleA406表指标对应关系 = new Tuple<string, string, string>()
        //{
        // ("存栏头数（含未断奶小猪）01", "期末存栏合计01", "A209")
        //};
        public DataTable GetSingleA406Msg(KeyValuePair<string, string> CityItem, KeyValuePair<string, string> CountryItem,
            KeyValuePair<string, string> Indicator, string WithYearSeasonA406NameNow, string WithYearSeasonA406NamePre,
            string WithYearSeasonA406NameYOY)
        {
            try
            {
                MyDBOperator adoForShowSingleMsg = MyDBOperator.DBOperatorInstance;
                if (adoForShowSingleMsg.CheckIfTableExists(WithYearSeasonA406NamePre) == false)
                {
                    MessageBox.Show("未导入" + WithYearSeasonA406NamePre + "报表");
                    return null;
                }
                if (adoForShowSingleMsg.CheckIfTableExists(WithYearSeasonA406NameYOY) == false)
                {
                    MessageBox.Show($"未导入{WithYearSeasonA406NameYOY}报表");
                    return null;
                }
                if (CityItem.Value.Equals("全部"))//全省分地市的情况
                {
                    adoForShowSingleMsg.MakeSureChosenTablesNotInDB(new string[] { "TableAllCountryListOfThisCity",
                    "No1LeftJoin",
                    "No2LeftJoin",
                    "No3LeftJoin","No4LeftJoin","No5LeftJoin","No6LeftJoin"});
                    //
                    string sqlForFindAllCityes = "select 统计区划名称 as 市,乡镇区划代码 as 市代码  from 全省区划代码 where Length(乡镇区划代码) <5 ";
                    DataTable dt1
                           = adoForShowSingleMsg.GetDataTable(sqlForFindAllCityes);
                    adoForShowSingleMsg.SetDatatableToANewTable(dt1, "TableAllCountryListOfThisCity");
                    //第一次left join
                    //先要用create语句
                    string sqlToCreateFirst = $"CREATE TABLE IF NOT EXISTS  No1LeftJoin (市 TEXT,市代码 TEXT,本季度{Indicator.Value} REAL);";
                    adoForShowSingleMsg.ExecuteBySql(sqlToCreateFirst);
                    string sqlToInsertFirst = $"INSERT INTO No1LeftJoin (市,市代码,本季度{Indicator.Value}) select t1.市 as 市,t1.市代码 as 市代码, 本季度{Indicator.Value} FROM" +
                        $"(TableAllCountryListOfThisCity as t1 left join (SELECT 市代码,sum([{Indicator.Value}]) as 本季度{Indicator.Value} from {WithYearSeasonA406NameNow} group by 市代码) as t2 on t1.市代码 = t2.市代码)";

                    adoForShowSingleMsg.ExecuteBySql(sqlToInsertFirst);
                    //第二次left join
                    string sqlToCreateSecond =
                        $"CREATE TABLE IF NOT EXISTS  No2LeftJoin (市 TEXT,市代码 TEXT,本季度{Indicator.Value} REAL,上季度{Indicator.Value} REAL);";
                    adoForShowSingleMsg.ExecuteBySql(sqlToCreateSecond);
                    string sqlToInsertSecond = $"INSERT INTO No2LeftJoin (市,市代码,本季度{Indicator.Value},上季度{Indicator.Value})" +
                                               $"select t1.市 as 市,t1.市代码 as 市代码, t1.[本季度{Indicator.Value}],t2.[上季度{Indicator.Value}]  from (No1LeftJoin as t1 left join " +
                    $"(select 市代码, sum([{Indicator.Value}]) as [上季度{Indicator.Value}] from {WithYearSeasonA406NamePre} group by 市代码) as t2 on t1.市代码 = t2.市代码)";
                    adoForShowSingleMsg.ExecuteBySql(sqlToInsertSecond);
                    //第三次left join
                    string sqlToCreateThird = $"CREATE TABLE IF NOT EXISTS  No3LeftJoin (市 TEXT,市代码 TEXT,本季度{Indicator.Value} REAL,上季度{Indicator.Value} REAL,同比季度{Indicator.Value} REAL,环比增量 REAL,同比增量 REAL,[环比增幅] REAL,[同比增幅] REAL);";
                    adoForShowSingleMsg.ExecuteBySql(sqlToCreateThird);
                    string sqlToInsertThird = $"INSERT INTO No3LeftJoin (市,市代码,本季度{Indicator.Value},上季度{Indicator.Value},同比季度{Indicator.Value},环比增量,同比增量,[环比增幅],[同比增幅]) SELECT  t1.市 as 市,t1.市代码 as 市代码,t1.[本季度{Indicator.Value}],t1.[上季度{Indicator.Value}],t2.[同比季度{Indicator.Value}]," +
                                              $" t1.[本季度{Indicator.Value}]-t1.[上季度{Indicator.Value}] as 环比增量, " +
                                              $"t1.[本季度{Indicator.Value}]-t2.[同比季度{Indicator.Value}] as 同比增量, " +
                                              $" iif(t1.[上季度{Indicator.Value}] = 0, iif(t1.[本季度{Indicator.Value}] > 0, '100', '0'), round((t1.[本季度{Indicator.Value}] / t1.[上季度{Indicator.Value}] - 1) * 100, 1)) as [环比增幅] , " +
                                              $" iif(t2.[同比季度{Indicator.Value}] = 0, iif(t1.[本季度{Indicator.Value}] > 0, '100', '0'), round((t1.[本季度{Indicator.Value}] / t2.[同比季度{Indicator.Value}] -1) *100, 1)) as [同比增幅]  " +
                                              $"   from ((select * from No2LeftJoin) as t1 left join (select 市代码,sum([{Indicator.Value}]) as [同比季度{Indicator.Value}] from {WithYearSeasonA406NameYOY} group by 市代码) as t2 on t1.市代码 = t2.市代码) ";
                    adoForShowSingleMsg.ExecuteBySql(sqlToInsertThird);




                    //查看指标有没有对应的规模户报表
                    if (DicA406表对应关系.ContainsKey(Indicator.Value))
                    {


                        string IndicatorNameInMass = this.DicA406表对应关系[Indicator.Value].Item1;
                        string Indicator所属规模户报表名 = this.DicA406表对应关系[Indicator.Value].Item2;
                        #region 从本季度规模户数据获取当前指标值
                        bool isThisIndicatorMassTableThisSeasonExists =
                                adoForShowSingleMsg.CheckIfTableExists(
                                    $"{Global.PlatformNow}{Global.StrYearNow}{Global.StrSeasonNow}{Indicator所属规模户报表名}");

                        string sqlToCreateFourth =
                            $"CREATE TABLE IF NOT EXISTS No4LeftJoin (市 TEXT,市代码 TEXT,[本季度{Indicator.Value}] REAL,[上季度{Indicator.Value}] REAL,[同比季度{Indicator.Value}] REAL ,环比增量 REAL,同比增量 REAL,[环比增幅] REAL,[同比增幅] REAL,本季度规模户{IndicatorNameInMass}之和 REAL);";
                        adoForShowSingleMsg.ExecuteBySql(sqlToCreateFourth);
                        if (isThisIndicatorMassTableThisSeasonExists)
                        {
                            string sqlToInsertFourth =
                                $"INSERT INTO No4LeftJoin (市,市代码,[本季度{Indicator.Value}],[上季度{Indicator.Value}],同比季度{Indicator.Value},环比增量,同比增量,环比增幅,同比增幅,本季度规模户{IndicatorNameInMass}之和)" +
                                $"SELECT t1.市 as 市,t1.市代码 as 市代码, t1.[本季度{Indicator.Value}],t1.[上季度{Indicator.Value}]," +
                                $"t1.[同比季度{Indicator.Value}],t1.环比增量,t1.同比增量,t1.[环比增幅],t1.[同比增幅],t2.[本季度规模户{IndicatorNameInMass}之和] " +
                                $"from (No3LeftJoin as t1 left join (select 市代码,sum([{IndicatorNameInMass}]) as [本季度规模户{IndicatorNameInMass}之和] from " +
                                $"{Global.PlatformNow}{Global.StrYearNow}{Global.StrSeasonNow}{Indicator所属规模户报表名} group by 市代码) as t2 on t1.市代码 = t2.市代码) ";
                            adoForShowSingleMsg.ExecuteBySql(sqlToInsertFourth);
                        }
                        else
                        {
                            string sqlToInsertFourth =
                                $"INSERT INTO No4LeftJoin (市,市代码,[本季度{Indicator.Value}],[上季度{Indicator.Value}],同比季度{Indicator.Value},环比增量,同比增量,环比增幅,同比增幅,本季度规模户{IndicatorNameInMass}之和)" +
                                $"SELECT 市,市代码,[本季度{Indicator.Value}],[上季度{Indicator.Value}],同比季度{Indicator.Value},环比增量,同比增量,环比增幅,同比增幅,0 from No3LeftJoin ";
                            adoForShowSingleMsg.ExecuteBySql(sqlToInsertFourth);
                        }
                        #endregion
                        #region 从本指标对应的上季度规模户数据表中获取该指标数据
                        string sqlToCreateFifth =
                                $"CREATE TABLE IF NOT EXISTS No5LeftJoin (市 TEXT,市代码 TEXT,[本季度{Indicator.Value}] REAL,[上季度{Indicator.Value}] REAL,同比季度{Indicator.Value} ,环比增量 REAL,同比增量 REAL,[环比增幅] REAL,[同比增幅] REAL,本季度规模户{IndicatorNameInMass}之和 REAL," +
                                $"上季度规模户{IndicatorNameInMass}之和 REAL);";
                        adoForShowSingleMsg.ExecuteBySql(sqlToCreateFifth);
                        bool isThisIndicatorMassTablePreSeasonExists = adoForShowSingleMsg.CheckIfTableExists($"{Global.PlatformPre}{Global.StrYearPre}{Global.StrSeasonPre}{Indicator所属规模户报表名}");
                        if (isThisIndicatorMassTablePreSeasonExists)
                        {
                            string sqlToInsertFifth = $"INSERT INTO No5LeftJoin (市,市代码,[本季度{Indicator.Value}],[上季度{Indicator.Value}],[同比季度{Indicator.Value}],环比增量,同比增量,环比增幅,同比增幅,本季度规模户{IndicatorNameInMass}之和,上季度规模户{IndicatorNameInMass}之和) " +
                                                      $"SELECT t1.市 as 市,t1.市代码 as 市代码, t1.[本季度{Indicator.Value}],t1.[上季度{Indicator.Value}]," +
                                                      $"t1.[同比季度{Indicator.Value}],t1.环比增量,t1.同比增量,t1.[环比增幅],t1.[同比增幅],t1.[本季度规模户{IndicatorNameInMass}之和],t2.上季度规模户{IndicatorNameInMass}之和 " +
                                                      $" from (No4LeftJoin as t1 left join (select 市代码,sum([{IndicatorNameInMass}]) as [上季度规模户{IndicatorNameInMass}之和] from " +
                                                      $"{Global.PlatformPre}{Global.StrYearPre}{Global.StrSeasonPre}{Indicator所属规模户报表名} group by 市代码) as t2 on t1.市代码 = t2.市代码) ";
                            adoForShowSingleMsg.ExecuteBySql(sqlToInsertFifth);
                        }
                        else
                        {
                            string sqlToInsertFifth=   $"INSERT INTO No5LeftJoin (市,市代码,[本季度{Indicator.Value}],[上季度{Indicator.Value}],同比季度{Indicator.Value},环比增量,同比增量,环比增幅,同比增幅,本季度规模户{IndicatorNameInMass}之和,上季度规模户{IndicatorNameInMass}之和) " +
                                $"SELECT 市,市代码,[本季度{Indicator.Value}],[上季度{Indicator.Value}],同比季度{Indicator.Value},环比增量,同比增量,环比增幅,同比增幅,本季度规模户{IndicatorNameInMass}之和,0 from No4LeftJoin ";
                            
                            adoForShowSingleMsg.ExecuteBySql(sqlToInsertFifth);
                        }

                        #endregion
                        #region   从同比季度的规模户表中获取该指标的规模户数据
                        string sqlToCreateSixth = $"CREATE TABLE IF NOT EXISTS No6LeftJoin (市 TEXT,市代码 TEXT,[本季度{Indicator.Value}] REAL,[上季度{Indicator.Value}] REAL,[同比季度{Indicator.Value}] REAL,环比增量 REAL,同比增量 REAL,[环比增幅] REAL,[同比增幅] REAL,本季度规模户{IndicatorNameInMass}之和 REAL," +
                                                  $"上季度规模户{IndicatorNameInMass}之和 REAL, [同比季度规模户{IndicatorNameInMass}之和] REAL) ;";
                        adoForShowSingleMsg.ExecuteBySql(sqlToCreateSixth);
                        bool isThisIndicatorMassTableYOYSeasonExists = adoForShowSingleMsg.CheckIfTableExists($"{Global.PlatformYOY}{Global.StrYearYOY}{Global.StrSeasonYOY}{Indicator所属规模户报表名}");
                        if (isThisIndicatorMassTableYOYSeasonExists)
                        {
                            string sqlToInsertSixth = $"INSERT INTO No6LeftJoin (市,市代码,[本季度{Indicator.Value}],[上季度{Indicator.Value}],[同比季度{Indicator.Value}],环比增量,同比增量,环比增幅,同比增幅,[本季度规模户{IndicatorNameInMass}之和],[上季度规模户{IndicatorNameInMass}之和],[同比季度规模户{IndicatorNameInMass}之和]) " +
                                                      $"SELECT t1.市 as 市,t1.市代码 as 市代码, t1.[本季度{Indicator.Value}],t1.[上季度{Indicator.Value}]," +
                                                      $"t1.[同比季度{Indicator.Value}],t1.环比增量,t1.同比增量,t1.[环比增幅],t1.[同比增幅],t1.[本季度规模户{IndicatorNameInMass}之和],t1.上季度规模户{IndicatorNameInMass}之和,t2.[同比季度规模户{IndicatorNameInMass}之和] " +
                                                      $" from (No5LeftJoin as t1 left join (select 市代码,sum([{IndicatorNameInMass}]) as [同比季度规模户{IndicatorNameInMass}之和] from " +
                                                      $"{Global.PlatformYOY}{Global.StrYearYOY}{Global.StrSeasonYOY}{Indicator所属规模户报表名} group by 市代码) as t2 on t1.市代码 = t2.市代码) ";
                            adoForShowSingleMsg.ExecuteBySql(sqlToInsertSixth);
                        }
                        else
                        {
                            string sqlToInsertSixth = $"INSERT INTO No6LeftJoin (市,市代码,[本季度{Indicator.Value}],[上季度{Indicator.Value}],[同比季度{Indicator.Value}],环比增量,同比增量,环比增幅,同比增幅,[本季度规模户{IndicatorNameInMass}之和],[上季度规模户{IndicatorNameInMass}之和],[同比季度规模户{IndicatorNameInMass}之和]) " +
                                                      $"SELECT 市,市代码,[本季度{Indicator.Value}],[上季度{Indicator.Value}],同比季度{Indicator.Value},环比增量,同比增量,环比增幅,同比增幅,本季度规模户{IndicatorNameInMass}之和,上季度规模户{IndicatorNameInMass}之和,0 from No5LeftJoin ";

                            adoForShowSingleMsg.ExecuteBySql(sqlToInsertSixth);
                        }
                        #endregion


                      

                        DataRow rowsum = adoForShowSingleMsg.GetDataTable($"select sum([本季度{Indicator.Value}]),sum([上季度{Indicator.Value}]),sum([同比季度{Indicator.Value}]) from No6LeftJoin").Rows[0];
                        double SumOfIndicatorNow = (double)rowsum[0];
                        double SumOfIndicatorPre = (double)rowsum[1];
                        double SumOfIndicatorYOY = (double)rowsum[2];
                        //给加入散户量和散户率
                        string[] SqlsForAddSomeColumns = new string[] {
                        $"ALTER TABLE No6LeftJoin ADD COLUMN 当季占比 double",
                        $"ALTER TABLE No6LeftJoin ADD COLUMN 上季占比 double",
                        $"ALTER TABLE No6LeftJoin ADD COLUMN 同比季度占比 double",
                        $"ALTER TABLE No6LeftJoin ADD COLUMN 本季度散户量 double",
                        $"ALTER TABLE No6LeftJoin ADD COLUMN 上季度散户量 double",
                        $"ALTER TABLE No6LeftJoin ADD COLUMN 同比季度散户量 double",
                        $"ALTER TABLE No6LeftJoin ADD COLUMN [本季度散户率(%)] double",
                        $"ALTER TABLE No6LeftJoin ADD COLUMN [上季度散户率(%)] double",
                        $"ALTER TABLE No6LeftJoin ADD COLUMN [同比季度散户率(%)] double",
                        $"ALTER TABLE No6LeftJoin ADD COLUMN 散户环比增量 double",
                        $"ALTER TABLE No6LeftJoin ADD COLUMN 散户同比增量 double",
                        $"ALTER TABLE No6LeftJoin ADD COLUMN [散户环比增速(%)] double",
                        $"ALTER TABLE No6LeftJoin ADD COLUMN [散户同比增速(%)] double",
                        };
                        foreach (string item in SqlsForAddSomeColumns)
                        {
                            adoForShowSingleMsg.ExecuteBySql(item);
                        }
                        string[] SqlsForUpdateColumns = new string[]
                        {
                            $"update  No6LeftJoin set [当季占比] = round ( [本季度{Indicator.Value}]/{SumOfIndicatorNow}*100,2)",
                            $"update  No6LeftJoin set [上季占比] = round ( [上季度{Indicator.Value}]/{SumOfIndicatorPre}*100,2)",
                            $"update  No6LeftJoin set [同比季度占比] = round ( [同比季度{Indicator.Value}]/{SumOfIndicatorYOY}*100,2)",
                            $"update  No6LeftJoin set [本季度散户量] = [本季度{Indicator.Value}] -[本季度规模户{IndicatorNameInMass}之和] where [本季度{Indicator.Value}] is not null",
                            $"update  No6LeftJoin set [上季度散户量] = [上季度{Indicator.Value}] -[上季度规模户{IndicatorNameInMass}之和]",
                            $"update  No6LeftJoin set [同比季度散户量] = [同比季度{Indicator.Value}] -[同比季度规模户{IndicatorNameInMass}之和]",
                            $"update  No6LeftJoin set [本季度散户率(%)] =Round( [本季度散户量] /[本季度{Indicator.Value}]*100,2) " +
                            $"where [本季度{Indicator.Value}] is not null and [本季度{Indicator.Value}] >0",
                            $"update  No6LeftJoin set [上季度散户率(%)] =Round( [上季度散户量] /[上季度{Indicator.Value}]*100,2) " +
                            $"where [上季度{Indicator.Value}] is not null and [上季度{Indicator.Value}] >0",
                            $"update  No6LeftJoin set [同比季度散户率(%)] =Round( [同比季度散户量] /[同比季度{Indicator.Value}]*100,2) " +
                            $"where [同比季度{Indicator.Value}] is not null and [同比季度{Indicator.Value}] >0",
                            $"update  No6LeftJoin set [散户环比增量] = [本季度散户量] -[上季度散户量]",//可能有问题
                            $"update  No6LeftJoin set [散户同比增量] = [本季度散户量] -[同比季度散户量]",
                        // $"update  No6LeftJoin set [散户环比增速(%)] = iif (上季度散户量 is null or 上季度散户量 = 0, iif(本季度散户量 is not null or 本季度散户量 > 0 ,100), round(([本季度散户量] /[上季度散户量] -1)*100,2))",
                           " UPDATE No6LeftJoin "+
"SET [散户环比增速(%)] = "+
    "CASE "+
       " WHEN 上季度散户量 IS NULL OR 上季度散户量 = 0 THEN "+
            "CASE "+
                "WHEN 本季度散户量 IS NOT NULL AND 本季度散户量 > 0 THEN 100 "+
                "ELSE 0  "+
            "END "+
        "ELSE "+
           " ROUND((CAST([本季度散户量] AS REAL) / 上季度散户量 - 1) * 100, 2) "+
    "END;"+
"    UPDATE No6LeftJoin"+
 " SET [散户同比增速(%)] = "+
  "  CASE" +
      "  WHEN 同比季度散户量 IS NULL OR 同比季度散户量 = 0 THEN "+
           " CASE" +
               " WHEN 本季度散户量 IS NOT NULL AND 本季度散户量 > 0 THEN 100 "+
              "  ELSE 0 "+
           " END"+
       " ELSE "+
           " ROUND((CAST([本季度散户量] AS REAL) / CAST(同比季度散户量 AS REAL) - 1) * 100, 2)"+
   " END;"

                            // $"update  No6LeftJoin set [散户同比增速(%)] = iif (同比季度散户量 is null or 同比季度散户量 = 0, iif(本季度散户量 is not null or 本季度散户量 > 0 ,100), round(([本季度散户量] /[同比季度散户量] -1)*100,2))"
                            //$"update  No6LeftJoin set [散户环比增速(%)] = iif (上季度散户量 is null or 上季度散户量 = 0, , round(([本季度散户量] /[上季度散户量] -1)*100,2))"
                        };
                        foreach (string item in SqlsForUpdateColumns)
                        {
                            adoForShowSingleMsg.ExecuteBySql(item);
                        }
                        DataTable dt = adoForShowSingleMsg.GetDataTable("select * from No6LeftJoin");
                        if (dt != null)
                        {
                            //dt.Columns.Add("本季度散户量", typeof(double));
                            //dt.Columns.Add("上季度散户量", typeof(double));
                            //dt.Columns.Add("同比季度散户量", typeof(double));
                            //dt.Columns.Add("本季度散户率", typeof(double));
                            //dt.Columns.Add("上季度散户率", typeof(double));
                            //dt.Columns.Add("同比季度散户率", typeof(double));
                            //dt.Columns.Add("散户环比增量", typeof(double));
                            //dt.Columns.Add("散户同比增量", typeof(double));
                            //dt.Columns.Add("散户环比增速", typeof(double));
                            //dt.Columns.Add("散户同比增速", typeof(double));
                            DataRow sumRow = dt.NewRow();
                            for (int i = 2; i < dt.Columns.Count; i++)
                            {
                                if (dt.Columns[i].ColumnName.Contains("%") == false)//如果不含有百分号,那么就是加起来就可以了
                                {
                                    var sum = dt.AsEnumerable().Sum(row =>
                                        Convert.ToDouble(row.Field<object>(i) ?? 0));
                                    sumRow[i] = sum;
                                }
                            }
                            sumRow[0] = "合计";
                            sumRow[1] = "合计";
                            if (!sumRow.IsNull("上季度" + Indicator.Value))
                            {
                                sumRow["环比增幅"] = Math.Round((double)sumRow["环比增量"] / (double)sumRow["上季度" + Indicator.Value] * 100, 2);
                            }
                            if (!sumRow.IsNull("同比季度" + Indicator.Value))
                            {
                                sumRow["同比增幅"] = Math.Round((double)sumRow["同比增量"] / (double)sumRow["同比季度" + Indicator.Value] * 100, 2);
                            }
                            sumRow["当季占比"] = 100d;
                            sumRow["上季占比"] = 100d;
                            sumRow["同比季度占比"] = 100d;
                            //散户率就不这么麻烦了
                            sumRow["本季度散户率(%)"] = Math.Round((double)sumRow["本季度散户量"] / (double)sumRow["本季度" + Indicator.Value] * 100, 2);
                            //上季度散户率
                            sumRow["上季度散户率(%)"] = Math.Round((double)sumRow["上季度散户量"] / (double)sumRow["上季度" + Indicator.Value] * 100, 2);
                            //同比季度散户率
                            sumRow["同比季度散户率(%)"] = Math.Round((double)sumRow["同比季度散户量"] / (double)sumRow["同比季度" + Indicator.Value] * 100, 2);
                            if (sumRow.IsNull($"上季度散户量") == false && (double)sumRow["上季度散户量"] > 0)
                            {
                                sumRow["散户环比增速(%)"] = Math.Round((double)sumRow["散户环比增量"] / (double)sumRow["上季度散户量"] * 100, 2);
                            }
                            if (sumRow.IsNull($"同比季度散户量") == false && (double)sumRow["同比季度散户量"] > 0)
                            {
                                sumRow["散户同比增速(%)"] = Math.Round((double)sumRow["散户同比增量"] / (double)sumRow["同比季度散户量"] * 100, 2);
                            }
                            dt.Rows.Add(sumRow);
                            return dt;
                        }

                    }
                    //如果没有跟规模户数据没有对应关系,此时只要显示同比环比即可
                    else
                    {

                        DataRow rowsum = adoForShowSingleMsg.GetDataTable($"select sum([本季度{Indicator.Value}]),sum([上季度{Indicator.Value}]),sum([同比季度{Indicator.Value}]) from No3LeftJoin").Rows[0];
                        double SumOfIndicatorNow = (double)rowsum[0];
                        double SumOfIndicatorPre = (double)rowsum[1];
                        double SumOfIndicatorYOY = (double)rowsum[2];
                        //计算占比
                        List<string> ListSqlsForAddColumnIntoNo3LeftJoin = new List<string>()
                {
                    $"alter table No3LeftJoin add column [当季占比(%)] double ",
                    $"alter table No3LeftJoin add column [上季度占比(%)] double ",
                    $"alter table No3LeftJoin add column [同比季度占比(%)] double ",
                };
                        if (SumOfIndicatorNow > 0)
                        {
                            ListSqlsForAddColumnIntoNo3LeftJoin.Add($"update  No3LeftJoin set [当季占比(%)] = round ( [本季度{Indicator.Value}]/{SumOfIndicatorNow}*100,2)");
                        }
                        if (SumOfIndicatorPre > 0)
                        {
                            ListSqlsForAddColumnIntoNo3LeftJoin.Add($"update  No3LeftJoin set [上季度占比(%)] = round ( [上季度{Indicator.Value}]/{SumOfIndicatorPre}*100,2)");
                        }
                        if (SumOfIndicatorYOY > 0)
                        {
                            ListSqlsForAddColumnIntoNo3LeftJoin.Add($"update  No3LeftJoin set [同比季度占比(%)] = round ( [同比季度{Indicator.Value}]/{SumOfIndicatorYOY}*100,2)");
                        }

                        foreach (var item in ListSqlsForAddColumnIntoNo3LeftJoin)
                        {
                            adoForShowSingleMsg.ExecuteBySql(item);
                        }
                        DataTable dt = adoForShowSingleMsg.GetDataTable($"select *  from No3LeftJoin");
                        DataRow SumRow = dt.NewRow();
                        SumRow[0] = "合计";

                        for (int i = 2; i < dt.Columns.Count; i++)
                        {
                            SumRow[i] = dt.AsEnumerable().Sum(row =>
                        Convert.ToDouble(row.Field<object>(i) ?? 0));
                            //把饼图需要的数据(就一个value)返回去

                        }
                        //double SumOfIndicatorNow = (from row in dt.AsEnumerable()
                        //                            select row.Field<double?>(2)).Sum().Value;
                        //var SumOfIndicatorPre = (from row in dt.AsEnumerable()
                        //                         select row.Field<double?>(3)).Sum().Value;
                        //var SumOfIndicatorYOY = (from row in dt.AsEnumerable()
                        //                         select row.Field<double?>(4)).Sum().Value;
                        SumRow[7] = Math.Round((SumOfIndicatorNow / SumOfIndicatorPre - 1) * 100, 2);
                        SumRow[8] = Math.Round((SumOfIndicatorNow / SumOfIndicatorYOY - 1) * 100, 2);
                        dt.Rows.Add(SumRow);

                        return dt;


                        //DataTable dt = adoForShowSingleMsg.GetDataTable($"select *  from No3LeftJoin");
                        //DataRow SumRow = dt.NewRow();
                        //SumRow[0] = SumRow[1] = "合计";

                        //for (int i = 2; i < dt.Columns.Count; i++)
                        //{
                        //    SumRow[i] = (from row in dt.AsEnumerable()
                        //                 select row.Field<double?>(i)).Sum().Value;
                        //    //把饼图需要的数据(就一个value)返回去

                        //}
                        //double SumOfIndicatorNow = (from row in dt.AsEnumerable()
                        //                            select row.Field<double?>(2)).Sum().Value;
                        //var SumOfIndicatorPre = (from row in dt.AsEnumerable()
                        //                         select row.Field<double?>(3)).Sum().Value;
                        //var SumOfIndicatorYOY = (from row in dt.AsEnumerable()
                        //                         select row.Field<double?>(4)).Sum().Value;
                        //SumRow[7] = Math.Round((SumOfIndicatorNow / SumOfIndicatorPre - 1) * 100, 2);
                        //SumRow[8] = Math.Round((SumOfIndicatorNow / SumOfIndicatorYOY - 1) * 100, 2);
                        //dt.Rows.Add(SumRow);

                        //return dt;
                    }
                    return null;







                }
                else if (CityItem.Value.Equals("全部") == false && CountryItem.Value.Equals("全部"))//全市的情况
                {
                    adoForShowSingleMsg.MakeSureChosenTablesNotInDB(new string[] { "TableAllCountryListOfThisCity",
                    "No1LeftJoin",
                    "No2LeftJoin",
                    "No3LeftJoin","No4LeftJoin","No5LeftJoin","No6LeftJoin"});
                    //
                    string sqlForFindAllCityes = $"select 统计区划名称 as 县,乡镇区划代码 as 县代码  from 全省区划代码 where 乡镇区划代码 like '{CityItem.Key}%' and Length(乡镇区划代码) = 6";
                    DataTable dt1
                           = adoForShowSingleMsg.GetDataTable(sqlForFindAllCityes);
                    adoForShowSingleMsg.SetDatatableToANewTable(dt1, "TableAllCountryListOfThisCity");
                    //第一次left join
                    //先要用create语句
                    string sqlToCreateFirst = $"CREATE TABLE IF NOT EXISTS  No1LeftJoin (县 TEXT,县代码 TEXT,本季度{Indicator.Value} REAL);";
                    adoForShowSingleMsg.ExecuteBySql(sqlToCreateFirst);
                    string sqlToInsertFirst = $"INSERT INTO No1LeftJoin (县,县代码,本季度{Indicator.Value}) select t1.县 as 县,t1.县代码 as 县代码, 本季度{Indicator.Value} FROM" +
                        $"(TableAllCountryListOfThisCity as t1 left join (SELECT 县代码,sum([{Indicator.Value}]) as 本季度{Indicator.Value} from {WithYearSeasonA406NameNow} group by 县代码) as t2 on t1.县代码 = t2.县代码)";

                    adoForShowSingleMsg.ExecuteBySql(sqlToInsertFirst);
                    //第二次left join
                    string sqlToCreateSecond =
                        $"CREATE TABLE IF NOT EXISTS  No2LeftJoin (县 TEXT,县代码 TEXT,本季度{Indicator.Value} REAL,上季度{Indicator.Value} REAL);";
                    adoForShowSingleMsg.ExecuteBySql(sqlToCreateSecond);
                    string sqlToInsertSecond = $"INSERT INTO No2LeftJoin (县,县代码,本季度{Indicator.Value},上季度{Indicator.Value})" +
                                               $"select t1.县 as 县,t1.县代码 as 县代码, t1.[本季度{Indicator.Value}],t2.[上季度{Indicator.Value}]  from (No1LeftJoin as t1 left join " +
                    $"(select 县代码, sum([{Indicator.Value}]) as [上季度{Indicator.Value}] from {WithYearSeasonA406NamePre} group by 县代码) as t2 on t1.县代码 = t2.县代码)";
                    adoForShowSingleMsg.ExecuteBySql(sqlToInsertSecond);
                    //第三次left join
                    string sqlToCreateThird = $"CREATE TABLE IF NOT EXISTS  No3LeftJoin (县 TEXT,县代码 TEXT,本季度{Indicator.Value} REAL,上季度{Indicator.Value} REAL,同比季度{Indicator.Value} REAL,环比增量 REAL,同比增量 REAL,[环比增幅] REAL,[同比增幅] REAL);";
                    adoForShowSingleMsg.ExecuteBySql(sqlToCreateThird);
                    string sqlToInsertThird = $"INSERT INTO No3LeftJoin (县,县代码,本季度{Indicator.Value},上季度{Indicator.Value},同比季度{Indicator.Value},环比增量,同比增量,[环比增幅],[同比增幅]) SELECT  t1.县 as 县,t1.县代码 as 县代码,t1.[本季度{Indicator.Value}],t1.[上季度{Indicator.Value}],t2.[同比季度{Indicator.Value}]," +
                                              $" t1.[本季度{Indicator.Value}]-t1.[上季度{Indicator.Value}] as 环比增量, " +
                                              $"t1.[本季度{Indicator.Value}]-t2.[同比季度{Indicator.Value}] as 同比增量, " +
                                              $" iif(t1.[上季度{Indicator.Value}] = 0, iif(t1.[本季度{Indicator.Value}] > 0, '100', '0'), round((t1.[本季度{Indicator.Value}] / t1.[上季度{Indicator.Value}] - 1) * 100, 1)) as [环比增幅] , " +
                                              $" iif(t2.[同比季度{Indicator.Value}] = 0, iif(t1.[本季度{Indicator.Value}] > 0, '100', '0'), round((t1.[本季度{Indicator.Value}] / t2.[同比季度{Indicator.Value}] -1) *100, 1)) as [同比增幅]  " +
                                              $"   from ((select * from No2LeftJoin) as t1 left join (select 县代码,sum([{Indicator.Value}]) as [同比季度{Indicator.Value}] from {WithYearSeasonA406NameYOY} group by 县代码) as t2 on t1.县代码 = t2.县代码) ";
                    adoForShowSingleMsg.ExecuteBySql(sqlToInsertThird);




                    //查看指标有没有对应的规模户报表
                    if (DicA406表对应关系.ContainsKey(Indicator.Value))
                    {

                        //string IndicatorNameInMass = this.DicA406表对应关系[Indicator.Value].Item1;
                        //string Indicator所属规模户报表名 = this.DicA406表对应关系[Indicator.Value].Item2;
                        //#region 从本季度规模户数据获取当前指标值
                        //string sqlToCreateFourth =
                        // $"CREATE TABLE IF NOT EXISTS No4LeftJoin (市 TEXT,市代码 TEXT,[本季度{Indicator.Value}] REAL,[上季度{Indicator.Value}] REAL,[同比季度{Indicator.Value}] REAL ,环比增量 REAL,同比增量 REAL,[环比增幅] REAL,[同比增幅] REAL,本季度规模户{IndicatorNameInMass}之和 REAL);";
                        //adoForShowSingleMsg.ExecuteBySql(sqlToCreateFourth);
                        //bool isThisIndicatorMassTableThisSeasonExists =
                        //        adoForShowSingleMsg.CheckIfTableExists(
                        //            $"{Global.PlatformNow}{Global.StrYearNow}{Global.StrSeasonNow}{Indicator所属规模户报表名}");
                        //if (isThisIndicatorMassTableThisSeasonExists)
                        //{
                        //    string sqlToInsertFourth =
                        //        $"INSERT INTO No4LeftJoin (市,市代码,[本季度{Indicator.Value}],[上季度{Indicator.Value}],同比季度{Indicator.Value},环比增量,同比增量,环比增幅,同比增幅,本季度规模户{IndicatorNameInMass}之和)" +
                        //        $"SELECT t1.市 as 市,t1.市代码 as 市代码, t1.[本季度{Indicator.Value}],t1.[上季度{Indicator.Value}]," +
                        //        $"t1.[同比季度{Indicator.Value}],t1.环比增量,t1.同比增量,t1.[环比增幅],t1.[同比增幅],t2.[本季度规模户{IndicatorNameInMass}之和] " +
                        //        $"from (No3LeftJoin as t1 left join (select 市代码,sum([{IndicatorNameInMass}]) as [本季度规模户{IndicatorNameInMass}之和] from " +
                        //        $"{Global.PlatformNow}{Global.StrYearNow}{Global.StrSeasonNow}{Indicator所属规模户报表名} group by 市代码) as t2 on t1.市代码 = t2.市代码) ";
                        //    adoForShowSingleMsg.ExecuteBySql(sqlToInsertFourth);
                        //}
                        //else
                        //{
                        //    string sqlToInsertFourth =
                        //        $"INSERT INTO No4LeftJoin (市,市代码,[本季度{Indicator.Value}],[上季度{Indicator.Value}],同比季度{Indicator.Value},环比增量,同比增量,环比增幅,同比增幅,本季度规模户{IndicatorNameInMass}之和)" +
                        //        $"SELECT 市,市代码,[本季度{Indicator.Value}],[上季度{Indicator.Value}],同比季度{Indicator.Value},环比增量,同比增量,环比增幅,同比增幅,0 from No3LeftJoin ";
                        //    adoForShowSingleMsg.ExecuteBySql(sqlToInsertFourth);
                        //}
                        //#endregion
                     





                        string IndicatorNameInMass = this.DicA406表对应关系[Indicator.Value].Item1;
                        string Indicator所属规模户报表名 = this.DicA406表对应关系[Indicator.Value].Item2;
                        #region    从本季度规模户数据获取当前指标值
                        //先建No4LeftJoin表
                        string sqlToCreateFourth =
                            $"CREATE TABLE IF NOT EXISTS No4LeftJoin (县 TEXT,县代码 TEXT,[本季度{Indicator.Value}] REAL,[上季度{Indicator.Value}] REAL,[同比季度{Indicator.Value}] REAL ,环比增量 REAL,同比增量 REAL,[环比增幅] REAL,[同比增幅] REAL,本季度规模户{IndicatorNameInMass}之和 REAL);";
                        adoForShowSingleMsg.ExecuteBySql(sqlToCreateFourth);
                        bool isThisIndicatorMassTableThisSeasonExists =
                                adoForShowSingleMsg.CheckIfTableExists(
                                    $"{Global.PlatformNow}{Global.StrYearNow}{Global.StrSeasonNow}{Indicator所属规模户报表名}");
                        if (isThisIndicatorMassTableThisSeasonExists)
                        {
                            string sqlToInsertFourth =
                               $"INSERT INTO No4LeftJoin (县,县代码,[本季度{Indicator.Value}],[上季度{Indicator.Value}],同比季度{Indicator.Value},环比增量,同比增量,环比增幅,同比增幅,本季度规模户{IndicatorNameInMass}之和)" +
                               $"SELECT t1.县 as 县,t1.县代码 as 县代码, t1.[本季度{Indicator.Value}],t1.[上季度{Indicator.Value}]," +
                               $"t1.[同比季度{Indicator.Value}],t1.环比增量,t1.同比增量,t1.[环比增幅],t1.[同比增幅],t2.[本季度规模户{IndicatorNameInMass}之和] " +
                               $"from (No3LeftJoin as t1 left join (select 县代码,sum([{IndicatorNameInMass}]) as [本季度规模户{IndicatorNameInMass}之和] from " +
                               $"{Global.PlatformNow}{Global.StrYearNow}{Global.StrSeasonNow}{Indicator所属规模户报表名} group by 县代码) as t2 on t1.县代码 = t2.县代码) ";
                            adoForShowSingleMsg.ExecuteBySql(sqlToInsertFourth);
                        }
                        else {
                            string sqlToInsertFourth =
                                    $"INSERT INTO No4LeftJoin (县,县代码,[本季度{Indicator.Value}],[上季度{Indicator.Value}],同比季度{Indicator.Value},环比增量,同比增量,环比增幅,同比增幅,本季度规模户{IndicatorNameInMass}之和)" +
                                    $"SELECT 县,县代码,[本季度{Indicator.Value}],[上季度{Indicator.Value}],同比季度{Indicator.Value},环比增量,同比增量,环比增幅,同比增幅,0 from No3LeftJoin ";
                            adoForShowSingleMsg.ExecuteBySql(sqlToInsertFourth);
                        }
                        #endregion
                        #region 从上季度规模户数据获取当前指标值对应的规模户数据
                        string sqlToCreateFifth =
                            $"CREATE TABLE IF NOT EXISTS No5LeftJoin (县 TEXT,县代码 TEXT,[本季度{Indicator.Value}] REAL,[上季度{Indicator.Value}] REAL,同比季度{Indicator.Value} ,环比增量 REAL,同比增量 REAL,[环比增幅] REAL,[同比增幅] REAL,本季度规模户{IndicatorNameInMass}之和 REAL," +
                            $"上季度规模户{IndicatorNameInMass}之和 REAL);";
                        adoForShowSingleMsg.ExecuteBySql(sqlToCreateFifth);
                        bool isThisIndicatorMassTablePreSeasonExists = adoForShowSingleMsg.CheckIfTableExists($"{Global.PlatformPre}{Global.StrYearPre}{Global.StrSeasonPre}{Indicator所属规模户报表名}");
                        if (isThisIndicatorMassTablePreSeasonExists)
                        {
                            string sqlToInsertFifth = $"INSERT INTO No5LeftJoin (县,县代码,[本季度{Indicator.Value}],[上季度{Indicator.Value}],[同比季度{Indicator.Value}],环比增量,同比增量,环比增幅,同比增幅,本季度规模户{IndicatorNameInMass}之和,上季度规模户{IndicatorNameInMass}之和) " +
                                                      $"SELECT t1.县 as 县,t1.县代码 as 县代码, t1.[本季度{Indicator.Value}],t1.[上季度{Indicator.Value}]," +
                                                      $"t1.[同比季度{Indicator.Value}],t1.环比增量,t1.同比增量,t1.[环比增幅],t1.[同比增幅],t1.[本季度规模户{IndicatorNameInMass}之和],t2.上季度规模户{IndicatorNameInMass}之和 " +
                                                      $" from (No4LeftJoin as t1 left join (select 县代码,sum([{IndicatorNameInMass}]) as [上季度规模户{IndicatorNameInMass}之和] from " +
                                                      $"{Global.PlatformPre}{Global.StrYearPre}{Global.StrSeasonPre}{Indicator所属规模户报表名} group by 县代码) as t2 on t1.县代码 = t2.县代码) ";
                            adoForShowSingleMsg.ExecuteBySql(sqlToInsertFifth);
                        }
                        else
                        {
                            string sqlToInsertFifth = $"INSERT INTO No5LeftJoin (县,县代码,[本季度{Indicator.Value}],[上季度{Indicator.Value}],同比季度{Indicator.Value},环比增量,同比增量,环比增幅,同比增幅,本季度规模户{IndicatorNameInMass}之和,上季度规模户{IndicatorNameInMass}之和) " +
                                $"SELECT 县,县代码,[本季度{Indicator.Value}],[上季度{Indicator.Value}],同比季度{Indicator.Value},环比增量,同比增量,环比增幅,同比增幅,本季度规模户{IndicatorNameInMass}之和,0 from No4LeftJoin ";

                            adoForShowSingleMsg.ExecuteBySql(sqlToInsertFifth);
                        }
                        #endregion

                    
                        #region   从同比季度的规模户表中获取该指标的规模户数据
                        string sqlToCreateSixth = $"CREATE TABLE IF NOT EXISTS No6LeftJoin (县 TEXT,县代码 TEXT,[本季度{Indicator.Value}] REAL,[上季度{Indicator.Value}] REAL,[同比季度{Indicator.Value}] REAL,环比增量 REAL,同比增量 REAL,[环比增幅] REAL,[同比增幅] REAL,本季度规模户{IndicatorNameInMass}之和 REAL," +
                                                  $"上季度规模户{IndicatorNameInMass}之和 REAL, [同比季度规模户{IndicatorNameInMass}之和] REAL) ;";
                        adoForShowSingleMsg.ExecuteBySql(sqlToCreateSixth);
                        bool isThisIndicatorMassTableYOYSeasonExists = adoForShowSingleMsg.CheckIfTableExists($"{Global.PlatformYOY}{Global.StrYearYOY}{Global.StrSeasonYOY}{Indicator所属规模户报表名}");
                        if (isThisIndicatorMassTableYOYSeasonExists)
                        {
                            string sqlToInsertSixth;
                            if (!Indicator.Value.Contains("蛋产"))
                            {
                                sqlToInsertSixth =
                                    $"INSERT INTO No6LeftJoin (县,县代码,[本季度{Indicator.Value}],[上季度{Indicator.Value}],[同比季度{Indicator.Value}],环比增量,同比增量,环比增幅,同比增幅,[本季度规模户{IndicatorNameInMass}之和],[上季度规模户{IndicatorNameInMass}之和],[同比季度规模户{IndicatorNameInMass}之和]) " +
                                    $"SELECT t1.县 as 县,t1.县代码 as 县代码, t1.[本季度{Indicator.Value}],t1.[上季度{Indicator.Value}]," +
                                    $"t1.[同比季度{Indicator.Value}],t1.环比增量,t1.同比增量,t1.[环比增幅],t1.[同比增幅],t1.[本季度规模户{IndicatorNameInMass}之和],t1.上季度规模户{IndicatorNameInMass}之和,t2.[同比季度规模户{IndicatorNameInMass}之和] " +
                                    $" from (No5LeftJoin as t1 left join (select 县代码,sum([{IndicatorNameInMass}]) as [同比季度规模户{IndicatorNameInMass}之和] from " +
                                    $"{Global.PlatformYOY}{Global.StrYearYOY}{Global.StrSeasonYOY}{Indicator所属规模户报表名} group by 县代码) as t2 on t1.县代码 = t2.县代码) ";
                            }
                            else
                            {
                                 sqlToInsertSixth =
                                    $"INSERT INTO No6LeftJoin (县,县代码,[本季度{Indicator.Value}],[上季度{Indicator.Value}],[同比季度{Indicator.Value}],环比增量,同比增量,环比增幅,同比增幅,[本季度规模户{IndicatorNameInMass}之和],[上季度规模户{IndicatorNameInMass}之和],[同比季度规模户{IndicatorNameInMass}之和]) " +
                                    $"SELECT t1.县 as 县,t1.县代码 as 县代码, t1.[本季度{Indicator.Value}],t1.[上季度{Indicator.Value}]," +
                                    $"t1.[同比季度{Indicator.Value}],t1.环比增量,t1.同比增量,t1.[环比增幅],t1.[同比增幅],t1.[本季度规模户{IndicatorNameInMass}之和]/1000,t1.上季度规模户{IndicatorNameInMass}之和/1000,t2.[同比季度规模户{IndicatorNameInMass}之和]/1000 " +
                                    $" from (No5LeftJoin as t1 left join (select 县代码,sum([{IndicatorNameInMass}]) as [同比季度规模户{IndicatorNameInMass}之和] from " +
                                    $"{Global.PlatformYOY}{Global.StrYearYOY}{Global.StrSeasonYOY}{Indicator所属规模户报表名} group by 县代码) as t2 on t1.县代码 = t2.县代码) ";
                            }

                            adoForShowSingleMsg.ExecuteBySql(sqlToInsertSixth);
                        }
                        else
                        {
                            string sqlToInsertSixth = $"INSERT INTO No6LeftJoin (县,县代码,[本季度{Indicator.Value}],[上季度{Indicator.Value}],[同比季度{Indicator.Value}],环比增量,同比增量,环比增幅,同比增幅,[本季度规模户{IndicatorNameInMass}之和],[上季度规模户{IndicatorNameInMass}之和],[同比季度规模户{IndicatorNameInMass}之和]) " +
                                                      $"SELECT 县,县代码,[本季度{Indicator.Value}],[上季度{Indicator.Value}],同比季度{Indicator.Value},环比增量,同比增量,环比增幅,同比增幅,本季度规模户{IndicatorNameInMass}之和,上季度规模户{IndicatorNameInMass}之和,0 from No5LeftJoin ";

                            adoForShowSingleMsg.ExecuteBySql(sqlToInsertSixth);
                        }
                        #endregion


































                        //string sqlToInsertFifth = $"INSERT INTO No5LeftJoin (县,县代码,[本季度{Indicator.Value}],[上季度{Indicator.Value}],[同比季度{Indicator.Value}],环比增量,同比增量,环比增幅,同比增幅,本季度规模户{IndicatorNameInMass}之和,上季度规模户{IndicatorNameInMass}之和) " +
                        //                         $"SELECT t1.县 as 县,t1.县代码 as 县代码, t1.[本季度{Indicator.Value}],t1.[上季度{Indicator.Value}]," +
                        //                         $"t1.[同比季度{Indicator.Value}],t1.环比增量,t1.同比增量,t1.[环比增幅],t1.[同比增幅],t1.[本季度规模户{IndicatorNameInMass}之和],t2.上季度规模户{IndicatorNameInMass}之和 " +
                        //                         $" from (No4LeftJoin as t1 left join (select 县代码,sum([{IndicatorNameInMass}]) as [上季度规模户{IndicatorNameInMass}之和] from " +
                        //    $"{Global.PlatformPre}{Global.StrYearPre}{Global.StrSeasonPre}{Indicator所属规模户报表名} group by 县代码) as t2 on t1.县代码 = t2.县代码) ";
                        //adoForShowSingleMsg.ExecuteBySql(sqlToInsertFifth);

                        //string sqlToCreateSixth = $"CREATE TABLE IF NOT EXISTS No6LeftJoin (县 TEXT,县代码 TEXT,[本季度{Indicator.Value}] REAL,[上季度{Indicator.Value}] REAL,[同比季度{Indicator.Value}] REAL,环比增量 REAL,同比增量 REAL,[环比增幅] REAL,[同比增幅] REAL,本季度规模户{IndicatorNameInMass}之和 REAL," +
                        //                          $"上季度规模户{IndicatorNameInMass}之和 REAL, [同比季度规模户{IndicatorNameInMass}之和] REAL) ;";
                        //adoForShowSingleMsg.ExecuteBySql(sqlToCreateSixth);
                        //string sqlToInsertSixth = $"INSERT INTO No6LeftJoin (县,县代码,[本季度{Indicator.Value}],[上季度{Indicator.Value}],[同比季度{Indicator.Value}],环比增量,同比增量,环比增幅,同比增幅,[本季度规模户{IndicatorNameInMass}之和],[上季度规模户{IndicatorNameInMass}之和],[同比季度规模户{IndicatorNameInMass}之和]) " +
                        //                         $"SELECT t1.县 as 县,t1.县代码 as 县代码, t1.[本季度{Indicator.Value}],t1.[上季度{Indicator.Value}]," +
                        //                         $"t1.[同比季度{Indicator.Value}],t1.环比增量,t1.同比增量,t1.[环比增幅],t1.[同比增幅],t1.[本季度规模户{IndicatorNameInMass}之和],t1.上季度规模户{IndicatorNameInMass}之和,t2.[同比季度规模户{IndicatorNameInMass}之和] " +
                        //                         $" from (No5LeftJoin as t1 left join (select 县代码,sum([{IndicatorNameInMass}]) as [同比季度规模户{IndicatorNameInMass}之和] from " +
                        //                         $"{Global.PlatformYOY}{Global.StrYearYOY}{Global.StrSeasonYOY}{Indicator所属规模户报表名} group by 县代码) as t2 on t1.县代码 = t2.县代码) ";
                        //adoForShowSingleMsg.ExecuteBySql(sqlToInsertSixth);
                        DataRow rowsum = adoForShowSingleMsg.GetDataTable($"select sum([本季度{Indicator.Value}]),sum([上季度{Indicator.Value}]),sum([同比季度{Indicator.Value}]) from No6LeftJoin").Rows[0];
                        double SumOfIndicatorNow = (double)rowsum[0];
                        double SumOfIndicatorPre = (double)rowsum[1];
                        double SumOfIndicatorYOY = (double)rowsum[2];

                        //给加入散户量和散户率
                        string[] SqlsForAddSomeColumns = new string[] {
                              $"ALTER TABLE No6LeftJoin ADD COLUMN 当季占比 double",
                        $"ALTER TABLE No6LeftJoin ADD COLUMN 上季占比 double",
                        $"ALTER TABLE No6LeftJoin ADD COLUMN 同比季度占比 double",
                        $"ALTER TABLE No6LeftJoin ADD COLUMN 本季度散户量 double",
                        $"ALTER TABLE No6LeftJoin ADD COLUMN 上季度散户量 double",
                        $"ALTER TABLE No6LeftJoin ADD COLUMN 同比季度散户量 double",
                        $"ALTER TABLE No6LeftJoin ADD COLUMN [本季度散户率(%)] double",
                        $"ALTER TABLE No6LeftJoin ADD COLUMN [上季度散户率(%)] double",
                        $"ALTER TABLE No6LeftJoin ADD COLUMN [同比季度散户率(%)] double",
                        $"ALTER TABLE No6LeftJoin ADD COLUMN 散户环比增量 double",
                        $"ALTER TABLE No6LeftJoin ADD COLUMN 散户同比增量 double",
                        $"ALTER TABLE No6LeftJoin ADD COLUMN [散户环比增速(%)] double",
                        $"ALTER TABLE No6LeftJoin ADD COLUMN [散户同比增速(%)] double",
                        };
                        foreach (string item in SqlsForAddSomeColumns)
                        {
                            adoForShowSingleMsg.ExecuteBySql(item);
                        }
                        string[] SqlsForUpdateColumns = new string[]
                        {
                            $"update  No6LeftJoin set [当季占比] = round ( [本季度{Indicator.Value}]/{SumOfIndicatorNow}*100,2)",
                            $"update  No6LeftJoin set [上季占比] = round ( [上季度{Indicator.Value}]/{SumOfIndicatorPre}*100,2)",
                            $"update  No6LeftJoin set [同比季度占比] = round ( [同比季度{Indicator.Value}]/{SumOfIndicatorYOY}*100,2)",
                            $"update  No6LeftJoin set [本季度散户量] = [本季度{Indicator.Value}] -[本季度规模户{IndicatorNameInMass}之和] where [本季度{Indicator.Value}] is not null",
                            $"update  No6LeftJoin set [上季度散户量] = [上季度{Indicator.Value}] -[上季度规模户{IndicatorNameInMass}之和]",
                            $"update  No6LeftJoin set [同比季度散户量] = [同比季度{Indicator.Value}] -[同比季度规模户{IndicatorNameInMass}之和]",
                            $"update  No6LeftJoin set [本季度散户率(%)] =Round( [本季度散户量] /[本季度{Indicator.Value}]*100,2) " +
                            $"where [本季度{Indicator.Value}] is not null and [本季度{Indicator.Value}] >0",
                            $"update  No6LeftJoin set [上季度散户率(%)] =Round( [上季度散户量] /[上季度{Indicator.Value}]*100,2) " +
                            $"where [上季度{Indicator.Value}] is not null and [上季度{Indicator.Value}] >0",
                            $"update  No6LeftJoin set [同比季度散户率(%)] =Round( [同比季度散户量] /[同比季度{Indicator.Value}]*100,2) " +
                            $"where [同比季度{Indicator.Value}] is not null and [同比季度{Indicator.Value}] >0",
                            $"update  No6LeftJoin set [散户环比增量] = [本季度散户量] -[上季度散户量]",//可能有问题
                            $"update  No6LeftJoin set [散户同比增量] = [本季度散户量] -[同比季度散户量]",
                        // $"update  No6LeftJoin set [散户环比增速(%)] = iif (上季度散户量 is null or 上季度散户量 = 0, iif(本季度散户量 is not null or 本季度散户量 > 0 ,100), round(([本季度散户量] /[上季度散户量] -1)*100,2))",
                           " UPDATE No6LeftJoin "+
"SET [散户环比增速(%)] = "+
    "CASE "+
       " WHEN 上季度散户量 IS NULL OR 上季度散户量 = 0 THEN "+
            "CASE "+
                "WHEN 本季度散户量 IS NOT NULL AND 本季度散户量 > 0 THEN 100 "+
                "ELSE 0  "+
            "END "+
        "ELSE "+
           " ROUND((CAST([本季度散户量] AS REAL) / 上季度散户量 - 1) * 100, 2) "+
    "END;"+
"    UPDATE No6LeftJoin"+
 " SET [散户同比增速(%)] = "+
  "  CASE" +
      "  WHEN 同比季度散户量 IS NULL OR 同比季度散户量 = 0 THEN "+
           " CASE" +
               " WHEN 本季度散户量 IS NOT NULL AND 本季度散户量 > 0 THEN 100 "+
              "  ELSE 0 "+
           " END"+
       " ELSE "+
           " ROUND((CAST([本季度散户量] AS REAL) / CAST(同比季度散户量 AS REAL) - 1) * 100, 2)"+
   " END;"

                       // $"update  No6LeftJoin set [散户同比增速(%)] = iif (同比季度散户量 is null or 同比季度散户量 = 0, iif(本季度散户量 is not null or 本季度散户量 > 0 ,100), round(([本季度散户量] /[同比季度散户量] -1)*100,2))"
                    //$"update  No6LeftJoin set [散户环比增速(%)] = iif (上季度散户量 is null or 上季度散户量 = 0, , round(([本季度散户量] /[上季度散户量] -1)*100,2))"
                        };
                        foreach (string item in SqlsForUpdateColumns)
                        {
                            adoForShowSingleMsg.ExecuteBySql(item);
                        }
                        DataTable dt = adoForShowSingleMsg.GetDataTable("select * from No6LeftJoin");
                        if (dt != null)
                        {
                            //dt.Columns.Add("本季度散户量", typeof(double));
                            //dt.Columns.Add("上季度散户量", typeof(double));
                            //dt.Columns.Add("同比季度散户量", typeof(double));
                            //dt.Columns.Add("本季度散户率", typeof(double));
                            //dt.Columns.Add("上季度散户率", typeof(double));
                            //dt.Columns.Add("同比季度散户率", typeof(double));
                            //dt.Columns.Add("散户环比增量", typeof(double));
                            //dt.Columns.Add("散户同比增量", typeof(double));
                            //dt.Columns.Add("散户环比增速", typeof(double));
                            //dt.Columns.Add("散户同比增速", typeof(double));
                            DataRow sumRow = dt.NewRow();
                            for (int i = 2; i < dt.Columns.Count; i++)
                            {
                                if (dt.Columns[i].ColumnName.Contains("%") == false)//如果不含有百分号,那么就是加起来就可以了
                                {
                                    var sum = dt.AsEnumerable().Sum(row =>
                                        Convert.ToDouble(row.Field<object>(i) ?? 0));
                                    sumRow[i] = sum;
                                }
                            }
                            sumRow[0] = "合计";
                            sumRow[1] = "合计";
                            if (!sumRow.IsNull("上季度" + Indicator.Value))
                            {
                                sumRow["环比增幅"] = Math.Round((double)sumRow["环比增量"] / (double)sumRow["上季度" + Indicator.Value] * 100, 2);
                            }
                            if (!sumRow.IsNull("同比季度" + Indicator.Value))
                            {
                                sumRow["同比增幅"] = Math.Round((double)sumRow["同比增量"] / (double)sumRow["同比季度" + Indicator.Value] * 100, 2);
                            }
                            sumRow["当季占比"] = 100d;
                            sumRow["上季占比"] = 100d;
                            sumRow["同比季度占比"] = 100d;
                            //散户率就不这么麻烦了
                            sumRow["本季度散户率(%)"] = Math.Round((double)sumRow["本季度散户量"] / (double)sumRow["本季度" + Indicator.Value] * 100, 2);
                            //上季度散户率
                            sumRow["上季度散户率(%)"] = Math.Round((double)sumRow["上季度散户量"] / (double)sumRow["上季度" + Indicator.Value] * 100, 2);
                            //同比季度散户率
                            sumRow["同比季度散户率(%)"] = Math.Round((double)sumRow["同比季度散户量"] / (double)sumRow["同比季度" + Indicator.Value] * 100, 2);
                            if (sumRow.IsNull($"上季度散户量") == false && (double)sumRow["上季度散户量"] > 0)
                            {
                                sumRow["散户环比增速(%)"] = Math.Round((double)sumRow["散户环比增量"] / (double)sumRow["上季度散户量"] * 100, 2);
                            }
                            if (sumRow.IsNull($"同比季度散户量") == false && (double)sumRow["同比季度散户量"] > 0)
                            {
                                sumRow["散户同比增速(%)"] = Math.Round((double)sumRow["散户同比增量"] / (double)sumRow["同比季度散户量"] * 100, 2);
                            }
                            dt.Rows.Add(sumRow);
                            return dt;
                        }

                    }
                    //如果没有跟规模户数据没有对应关系,此时只要显示同比环比即可
                    else
                    {
                        DataRow rowsum = adoForShowSingleMsg.GetDataTable($"select sum([本季度{Indicator.Value}]),sum([上季度{Indicator.Value}]),sum([同比季度{Indicator.Value}]) from No3LeftJoin").Rows[0];
                        double SumOfIndicatorNow = (double)rowsum[0];
                        double SumOfIndicatorPre = (double)rowsum[1];
                        double SumOfIndicatorYOY = (double)rowsum[2];
                        //计算占比
                        List<string> ListSqlsForAddColumnIntoNo3LeftJoin = new List<string>()
                {
                    $"alter table No3LeftJoin add column [当季占比(%)] double ",
                    $"alter table No3LeftJoin add column [上季度占比(%)] double ",
                    $"alter table No3LeftJoin add column [同比季度占比(%)] double ",
                };
                        if (SumOfIndicatorNow > 0)
                        {
                            ListSqlsForAddColumnIntoNo3LeftJoin.Add($"update  No3LeftJoin set [当季占比(%)] = round ( [本季度{Indicator.Value}]/{SumOfIndicatorNow}*100,2)");
                        }
                        if (SumOfIndicatorPre > 0)
                        {
                            ListSqlsForAddColumnIntoNo3LeftJoin.Add($"update  No3LeftJoin set [上季度占比(%)] = round ( [上季度{Indicator.Value}]/{SumOfIndicatorPre}*100,2)");
                        }
                        if (SumOfIndicatorYOY > 0)
                        {
                            ListSqlsForAddColumnIntoNo3LeftJoin.Add($"update  No3LeftJoin set [同比季度占比(%)] = round ( [同比季度{Indicator.Value}]/{SumOfIndicatorYOY}*100,2)");
                        }

                        foreach (var item in ListSqlsForAddColumnIntoNo3LeftJoin)
                        {
                            adoForShowSingleMsg.ExecuteBySql(item);
                        }
                        DataTable dt = adoForShowSingleMsg.GetDataTable($"select *  from No3LeftJoin");
                        DataRow SumRow = dt.NewRow();
                        SumRow[0] = "合计";

                        for (int i = 2; i < dt.Columns.Count; i++)
                        {
                            SumRow[i] = dt.AsEnumerable().Sum(row =>
                                Convert.ToDouble(row.Field<object>(i) ?? 0));
                            //把饼图需要的数据(就一个value)返回去

                        }
                        //double SumOfIndicatorNow = (from row in dt.AsEnumerable()
                        //                            select row.Field<double?>(2)).Sum().Value;
                        //var SumOfIndicatorPre = (from row in dt.AsEnumerable()
                        //                         select row.Field<double?>(3)).Sum().Value;
                        //var SumOfIndicatorYOY = (from row in dt.AsEnumerable()
                        //                         select row.Field<double?>(4)).Sum().Value;
                        SumRow[7] = Math.Round((SumOfIndicatorNow / SumOfIndicatorPre - 1) * 100, 2);
                        SumRow[8] = Math.Round((SumOfIndicatorNow / SumOfIndicatorYOY - 1) * 100, 2);
                        dt.Rows.Add(SumRow);

                        return dt;
                    }
                    return null;






                }
                else if (CountryItem.Value.Equals("全部") == false)//某个具体县的情况
                {
                    adoForShowSingleMsg.MakeSureChosenTablesNotInDB(new string[] { "TableAllCountryListOfThisCity",
                    "No1LeftJoin",
                    "No2LeftJoin",
                    "No3LeftJoin","No4LeftJoin","No5LeftJoin","No6LeftJoin"});
                    //
                    string sqlForFindAllCityes = $"select 统计区划名称 as 乡,乡镇区划代码 as 乡镇代码  from 全省区划代码 where 乡镇区划代码 like '{CountryItem.Key}%' and Length(乡镇区划代码) = 9";
                    DataTable dt1
                           = adoForShowSingleMsg.GetDataTable(sqlForFindAllCityes);
                    adoForShowSingleMsg.SetDatatableToANewTable(dt1, "TableAllCountryListOfThisCity");
                    //第一次left join
                    //先要用create语句
                    string sqlToCreateFirst = $"CREATE TABLE IF NOT EXISTS  No1LeftJoin (乡 TEXT,乡镇代码 TEXT,本季度{Indicator.Value} REAL);";
                    adoForShowSingleMsg.ExecuteBySql(sqlToCreateFirst);
                    string sqlToInsertFirst = $"INSERT INTO No1LeftJoin (乡,乡镇代码,本季度{Indicator.Value}) select t1.乡 as 乡,t1.乡镇代码 as 乡镇代码, 本季度{Indicator.Value} FROM" +
                        $"(TableAllCountryListOfThisCity as t1 left join (SELECT 乡镇代码,sum([{Indicator.Value}]) as 本季度{Indicator.Value} from {WithYearSeasonA406NameNow} group by 乡镇代码) as t2 on t1.乡镇代码 = t2.乡镇代码)";

                    adoForShowSingleMsg.ExecuteBySql(sqlToInsertFirst);
                    //第二次left join
                    string sqlToCreateSecond =
                        $"CREATE TABLE IF NOT EXISTS  No2LeftJoin (乡 TEXT,乡镇代码 TEXT,本季度{Indicator.Value} REAL,上季度{Indicator.Value} REAL);";
                    adoForShowSingleMsg.ExecuteBySql(sqlToCreateSecond);
                    string sqlToInsertSecond = $"INSERT INTO No2LeftJoin (乡,乡镇代码,本季度{Indicator.Value},上季度{Indicator.Value})" +
                                               $"select t1.乡 as 乡,t1.乡镇代码 as 乡镇代码, t1.[本季度{Indicator.Value}],t2.[上季度{Indicator.Value}]  from (No1LeftJoin as t1 left join " +
                    $"(select 乡镇代码, sum([{Indicator.Value}]) as [上季度{Indicator.Value}] from {WithYearSeasonA406NamePre} group by 乡镇代码) as t2 on t1.乡镇代码 = t2.乡镇代码)";
                    adoForShowSingleMsg.ExecuteBySql(sqlToInsertSecond);
                    //第三次left join
                    string sqlToCreateThird = $"CREATE TABLE IF NOT EXISTS  No3LeftJoin (乡 TEXT,乡镇代码 TEXT,本季度{Indicator.Value} REAL,上季度{Indicator.Value} REAL,同比季度{Indicator.Value} REAL,环比增量 REAL,同比增量 REAL,[环比增幅] REAL,[同比增幅] REAL);";
                    adoForShowSingleMsg.ExecuteBySql(sqlToCreateThird);
                    string sqlToInsertThird = $"INSERT INTO No3LeftJoin (乡,乡镇代码,本季度{Indicator.Value},上季度{Indicator.Value},同比季度{Indicator.Value},环比增量,同比增量,[环比增幅],[同比增幅]) SELECT  t1.乡 as 乡,t1.乡镇代码 as 乡镇代码,t1.[本季度{Indicator.Value}],t1.[上季度{Indicator.Value}],t2.[同比季度{Indicator.Value}]," +
                                              $" t1.[本季度{Indicator.Value}]-t1.[上季度{Indicator.Value}] as 环比增量, " +
                                              $"t1.[本季度{Indicator.Value}]-t2.[同比季度{Indicator.Value}] as 同比增量, " +
                                              $" iif(t1.[上季度{Indicator.Value}] = 0, iif(t1.[本季度{Indicator.Value}] > 0, '100', '0'), round((t1.[本季度{Indicator.Value}] / t1.[上季度{Indicator.Value}] - 1) * 100, 1)) as [环比增幅] , " +
                                              $" iif(t2.[同比季度{Indicator.Value}] = 0, iif(t1.[本季度{Indicator.Value}] > 0, '100', '0'), round((t1.[本季度{Indicator.Value}] / t2.[同比季度{Indicator.Value}] -1) *100, 1)) as [同比增幅]  " +
                                              $"   from ((select * from No2LeftJoin) as t1 left join (select 乡镇代码,sum([{Indicator.Value}]) as [同比季度{Indicator.Value}] from {WithYearSeasonA406NameYOY} group by 乡镇代码) as t2 on t1.乡镇代码 = t2.乡镇代码) ";
                    adoForShowSingleMsg.ExecuteBySql(sqlToInsertThird);




                    //查看指标有没有对应的规模户报表
                    if (DicA406表对应关系.ContainsKey(Indicator.Value))
                    {


                        //string IndicatorNameInMass = this.DicA406表对应关系[Indicator.Value].Item1;
                        //string Indicator所属规模户报表名 = this.DicA406表对应关系[Indicator.Value].Item2;
                        //string sqlToCreateFourth =
                        //    $"CREATE TABLE IF NOT EXISTS No4LeftJoin (乡 TEXT,乡镇代码 TEXT,[本季度{Indicator.Value}] REAL,[上季度{Indicator.Value}] REAL,[同比季度{Indicator.Value}] REAL ,环比增量 REAL,同比增量 REAL,[环比增幅] REAL,[同比增幅] REAL,本季度规模户{IndicatorNameInMass}之和 REAL);";
                        //adoForShowSingleMsg.ExecuteBySql(sqlToCreateFourth);
                        //string sqlToInsertFourth =
                        //    $"INSERT INTO No4LeftJoin (乡,乡镇代码,[本季度{Indicator.Value}],[上季度{Indicator.Value}],同比季度{Indicator.Value},环比增量,同比增量,环比增幅,同比增幅,本季度规模户{IndicatorNameInMass}之和)" +
                        //    $"SELECT t1.乡 as 乡,t1.乡镇代码 as 乡镇代码, t1.[本季度{Indicator.Value}],t1.[上季度{Indicator.Value}]," +
                        //    $"t1.[同比季度{Indicator.Value}],t1.环比增量,t1.同比增量,t1.[环比增幅],t1.[同比增幅],t2.[本季度规模户{IndicatorNameInMass}之和] " +
                        //    $"from (No3LeftJoin as t1 left join (select 乡镇代码,sum([{IndicatorNameInMass}]) as [本季度规模户{IndicatorNameInMass}之和] from " +
                        //    $"{Global.PlatformNow}{Global.StrYearNow}{Global.StrSeasonNow}{Indicator所属规模户报表名} group by 乡镇代码) as t2 on t1.乡镇代码 = t2.乡镇代码) ";
                        //adoForShowSingleMsg.ExecuteBySql(sqlToInsertFourth);

                        //string sqlToCreateFifth =
                        //    $"CREATE TABLE IF NOT EXISTS No5LeftJoin (乡 TEXT,乡镇代码 TEXT,[本季度{Indicator.Value}] REAL,[上季度{Indicator.Value}] REAL,同比季度{Indicator.Value} ,环比增量 REAL,同比增量 REAL,[环比增幅] REAL,[同比增幅] REAL,本季度规模户{IndicatorNameInMass}之和 REAL," +
                        //    $"上季度规模户{IndicatorNameInMass}之和 REAL);";
                        //adoForShowSingleMsg.ExecuteBySql(sqlToCreateFifth);
                        //string sqlToInsertFifth = $"INSERT INTO No5LeftJoin (乡,乡镇代码,[本季度{Indicator.Value}],[上季度{Indicator.Value}],[同比季度{Indicator.Value}],环比增量,同比增量,环比增幅,同比增幅,本季度规模户{IndicatorNameInMass}之和,上季度规模户{IndicatorNameInMass}之和) " +
                        //                         $"SELECT t1.乡 as 乡,t1.乡镇代码 as 乡镇代码, t1.[本季度{Indicator.Value}],t1.[上季度{Indicator.Value}]," +
                        //                         $"t1.[同比季度{Indicator.Value}],t1.环比增量,t1.同比增量,t1.[环比增幅],t1.[同比增幅],t1.[本季度规模户{IndicatorNameInMass}之和],t2.上季度规模户{IndicatorNameInMass}之和 " +
                        //                         $" from (No4LeftJoin as t1 left join (select 乡镇代码,sum([{IndicatorNameInMass}]) as [上季度规模户{IndicatorNameInMass}之和] from " +
                        //    $"{Global.PlatformPre}{Global.StrYearPre}{Global.StrSeasonPre}{Indicator所属规模户报表名} group by 乡镇代码) as t2 on t1.乡镇代码 = t2.乡镇代码) ";
                        //adoForShowSingleMsg.ExecuteBySql(sqlToInsertFifth);

                        //string sqlToCreateSixth = $"CREATE TABLE IF NOT EXISTS No6LeftJoin (乡 TEXT,乡镇代码 TEXT,[本季度{Indicator.Value}] REAL,[上季度{Indicator.Value}] REAL,[同比季度{Indicator.Value}] REAL,环比增量 REAL,同比增量 REAL,[环比增幅] REAL,[同比增幅] REAL,本季度规模户{IndicatorNameInMass}之和 REAL," +
                        //                          $"上季度规模户{IndicatorNameInMass}之和 REAL, [同比季度规模户{IndicatorNameInMass}之和] REAL) ;";
                        //adoForShowSingleMsg.ExecuteBySql(sqlToCreateSixth);
                        //string sqlToInsertSixth = $"INSERT INTO No6LeftJoin (乡,乡镇代码,[本季度{Indicator.Value}],[上季度{Indicator.Value}],[同比季度{Indicator.Value}],环比增量,同比增量,环比增幅,同比增幅,[本季度规模户{IndicatorNameInMass}之和],[上季度规模户{IndicatorNameInMass}之和],[同比季度规模户{IndicatorNameInMass}之和]) " +
                        //                         $"SELECT t1.乡 as 乡,t1.乡镇代码 as 乡镇代码, t1.[本季度{Indicator.Value}],t1.[上季度{Indicator.Value}]," +
                        //                         $"t1.[同比季度{Indicator.Value}],t1.环比增量,t1.同比增量,t1.[环比增幅],t1.[同比增幅],t1.[本季度规模户{IndicatorNameInMass}之和],t1.上季度规模户{IndicatorNameInMass}之和,t2.[同比季度规模户{IndicatorNameInMass}之和] " +
                        //                         $" from (No5LeftJoin as t1 left join (select 乡镇代码,sum([{IndicatorNameInMass}]) as [同比季度规模户{IndicatorNameInMass}之和] from " +
                        //                         $"{Global.PlatformYOY}{Global.StrYearYOY}{Global.StrSeasonYOY}{Indicator所属规模户报表名} group by 乡镇代码) as t2 on t1.乡镇代码 = t2.乡镇代码) ";
                        //adoForShowSingleMsg.ExecuteBySql(sqlToInsertSixth);

                        string IndicatorNameInMass = this.DicA406表对应关系[Indicator.Value].Item1;
                        string Indicator所属规模户报表名 = this.DicA406表对应关系[Indicator.Value].Item2;
                        #region 从本季度规模户数据获取当前指标值
                        bool isThisIndicatorMassTableThisSeasonExists =
                                adoForShowSingleMsg.CheckIfTableExists(
                                    $"{Global.PlatformNow}{Global.StrYearNow}{Global.StrSeasonNow}{Indicator所属规模户报表名}");

                        string sqlToCreateFourth =
                            $"CREATE TABLE IF NOT EXISTS No4LeftJoin (乡 TEXT,乡镇代码 TEXT,[本季度{Indicator.Value}] REAL,[上季度{Indicator.Value}] REAL,[同比季度{Indicator.Value}] REAL ,环比增量 REAL,同比增量 REAL,[环比增幅] REAL,[同比增幅] REAL,本季度规模户{IndicatorNameInMass}之和 REAL);";
                        adoForShowSingleMsg.ExecuteBySql(sqlToCreateFourth);
                        if (isThisIndicatorMassTableThisSeasonExists)
                        {
                            string sqlToInsertFourth =
                                $"INSERT INTO No4LeftJoin (乡,乡镇代码,[本季度{Indicator.Value}],[上季度{Indicator.Value}],同比季度{Indicator.Value},环比增量,同比增量,环比增幅,同比增幅,本季度规模户{IndicatorNameInMass}之和)" +
                                $"SELECT t1.乡 as 乡,t1.乡镇代码 as 乡镇代码, t1.[本季度{Indicator.Value}],t1.[上季度{Indicator.Value}]," +
                                $"t1.[同比季度{Indicator.Value}],t1.环比增量,t1.同比增量,t1.[环比增幅],t1.[同比增幅],COALESCE(t2.[本季度规模户{IndicatorNameInMass}之和],0) " +
                                $"from (No3LeftJoin as t1 left join (select 乡镇代码,sum([{IndicatorNameInMass}]) as [本季度规模户{IndicatorNameInMass}之和] from " +
                                $"{Global.PlatformNow}{Global.StrYearNow}{Global.StrSeasonNow}{Indicator所属规模户报表名} group by 乡镇代码) as t2 on t1.乡镇代码 = t2.乡镇代码) ";
                            adoForShowSingleMsg.ExecuteBySql(sqlToInsertFourth);
                        }
                        else
                        {
                            string sqlToInsertFourth =
                                $"INSERT INTO No4LeftJoin (乡,乡镇代码,[本季度{Indicator.Value}],[上季度{Indicator.Value}],同比季度{Indicator.Value},环比增量,同比增量,环比增幅,同比增幅,本季度规模户{IndicatorNameInMass}之和)" +
                                $"SELECT 乡,乡镇代码,[本季度{Indicator.Value}],[上季度{Indicator.Value}],同比季度{Indicator.Value},环比增量,同比增量,环比增幅,同比增幅,0 from No3LeftJoin ";
                            adoForShowSingleMsg.ExecuteBySql(sqlToInsertFourth);
                        }
                        #endregion
                        #region 从本指标对应的上季度规模户数据表中获取该指标数据
                        string sqlToCreateFifth =
                                $"CREATE TABLE IF NOT EXISTS No5LeftJoin (乡 TEXT,乡镇代码 TEXT,[本季度{Indicator.Value}] REAL,[上季度{Indicator.Value}] REAL,同比季度{Indicator.Value} ,环比增量 REAL,同比增量 REAL,[环比增幅] REAL,[同比增幅] REAL,本季度规模户{IndicatorNameInMass}之和 REAL," +
                                $"上季度规模户{IndicatorNameInMass}之和 REAL);";
                        adoForShowSingleMsg.ExecuteBySql(sqlToCreateFifth);
                        bool isThisIndicatorMassTablePreSeasonExists = adoForShowSingleMsg.CheckIfTableExists($"{Global.PlatformPre}{Global.StrYearPre}{Global.StrSeasonPre}{Indicator所属规模户报表名}");
                        if (isThisIndicatorMassTablePreSeasonExists)
                        {
                            string sqlToInsertFifth = $"INSERT INTO No5LeftJoin (乡,乡镇代码,[本季度{Indicator.Value}],[上季度{Indicator.Value}],[同比季度{Indicator.Value}],环比增量,同比增量,环比增幅,同比增幅,本季度规模户{IndicatorNameInMass}之和,上季度规模户{IndicatorNameInMass}之和) " +
                                                      $"SELECT t1.乡 as 乡,t1.乡镇代码 as 乡镇代码, t1.[本季度{Indicator.Value}],t1.[上季度{Indicator.Value}]," +
                                                      $"t1.[同比季度{Indicator.Value}],t1.环比增量,t1.同比增量,t1.[环比增幅],t1.[同比增幅],t1.[本季度规模户{IndicatorNameInMass}之和],COALESCE(t2.上季度规模户{IndicatorNameInMass}之和,0) " +
                                                      $" from (No4LeftJoin as t1 left join (select 乡镇代码,sum([{IndicatorNameInMass}]) as [上季度规模户{IndicatorNameInMass}之和] from " +
                                                      $"{Global.PlatformPre}{Global.StrYearPre}{Global.StrSeasonPre}{Indicator所属规模户报表名} group by 乡镇代码) as t2 on t1.乡镇代码 = t2.乡镇代码) ";
                            adoForShowSingleMsg.ExecuteBySql(sqlToInsertFifth);
                        }
                        else
                        {
                            string sqlToInsertFifth = $"INSERT INTO No5LeftJoin (乡,乡镇代码,[本季度{Indicator.Value}],[上季度{Indicator.Value}],同比季度{Indicator.Value},环比增量,同比增量,环比增幅,同比增幅,本季度规模户{IndicatorNameInMass}之和,上季度规模户{IndicatorNameInMass}之和) " +
                                $"SELECT 乡,乡镇代码,[本季度{Indicator.Value}],[上季度{Indicator.Value}],同比季度{Indicator.Value},环比增量,同比增量,环比增幅,同比增幅,本季度规模户{IndicatorNameInMass}之和,0 from No4LeftJoin ";

                            adoForShowSingleMsg.ExecuteBySql(sqlToInsertFifth);
                        }

                        #endregion
                        #region   从同比季度的规模户表中获取该指标的规模户数据
                        string sqlToCreateSixth = $"CREATE TABLE IF NOT EXISTS No6LeftJoin (乡 TEXT,乡镇代码 TEXT,[本季度{Indicator.Value}] REAL,[上季度{Indicator.Value}] REAL,[同比季度{Indicator.Value}] REAL,环比增量 REAL,同比增量 REAL,[环比增幅] REAL,[同比增幅] REAL,本季度规模户{IndicatorNameInMass}之和 REAL," +
                                                  $"上季度规模户{IndicatorNameInMass}之和 REAL, [同比季度规模户{IndicatorNameInMass}之和] REAL) ;";
                        adoForShowSingleMsg.ExecuteBySql(sqlToCreateSixth);
                        bool isThisIndicatorMassTableYOYSeasonExists = adoForShowSingleMsg.CheckIfTableExists($"{Global.PlatformYOY}{Global.StrYearYOY}{Global.StrSeasonYOY}{Indicator所属规模户报表名}");
                        if (isThisIndicatorMassTableYOYSeasonExists)
                        {
                            string sqlToInsertSixth = $"INSERT INTO No6LeftJoin (乡,乡镇代码,[本季度{Indicator.Value}],[上季度{Indicator.Value}],[同比季度{Indicator.Value}],环比增量,同比增量,环比增幅,同比增幅,[本季度规模户{IndicatorNameInMass}之和],[上季度规模户{IndicatorNameInMass}之和],[同比季度规模户{IndicatorNameInMass}之和]) " +
                                                      $"SELECT t1.乡 as 乡,t1.乡镇代码 as 乡镇代码, t1.[本季度{Indicator.Value}],t1.[上季度{Indicator.Value}]," +
                                                      $"t1.[同比季度{Indicator.Value}],t1.环比增量,t1.同比增量,t1.[环比增幅],t1.[同比增幅],t1.[本季度规模户{IndicatorNameInMass}之和],t1.上季度规模户{IndicatorNameInMass}之和,COALESCE(t2.[同比季度规模户{IndicatorNameInMass}之和],0) " +
                                                      $" from (No5LeftJoin as t1 left join (select 乡镇代码,sum([{IndicatorNameInMass}]) as [同比季度规模户{IndicatorNameInMass}之和] from " +
                                                      $"{Global.PlatformYOY}{Global.StrYearYOY}{Global.StrSeasonYOY}{Indicator所属规模户报表名} group by 乡镇代码) as t2 on t1.乡镇代码 = t2.乡镇代码) ";
                            adoForShowSingleMsg.ExecuteBySql(sqlToInsertSixth);
                        }
                        else
                        {
                            string sqlToInsertSixth = $"INSERT INTO No6LeftJoin (乡,乡镇代码,[本季度{Indicator.Value}],[上季度{Indicator.Value}],[同比季度{Indicator.Value}],环比增量,同比增量,环比增幅,同比增幅," +
                                                      $"COALESCE([本季度规模户{IndicatorNameInMass}之和],0),COALESCE([上季度规模户{IndicatorNameInMass}之和],0),COALESCE([同比季度规模户{IndicatorNameInMass}之和],0) " +
                                                      $"SELECT 乡,乡镇代码,[本季度{Indicator.Value}],[上季度{Indicator.Value}],同比季度{Indicator.Value},环比增量,同比增量,环比增幅,同比增幅,本季度规模户{IndicatorNameInMass}之和,上季度规模户{IndicatorNameInMass}之和,0 from No5LeftJoin ";

                            adoForShowSingleMsg.ExecuteBySql(sqlToInsertSixth);
                        }
                        #endregion


                        DataRow rowsum = adoForShowSingleMsg.GetDataTable($"select sum([本季度{Indicator.Value}]),sum([上季度{Indicator.Value}]),sum([同比季度{Indicator.Value}]) from No6LeftJoin").Rows[0];
                        double SumOfIndicatorNow = (double)rowsum[0];
                        double SumOfIndicatorPre = (double)rowsum[1];
                        double SumOfIndicatorYOY = (double)rowsum[2];

                        //给加入散户量和散户率
                        string[] SqlsForAddSomeColumns = new string[] {
                              $"ALTER TABLE No6LeftJoin ADD COLUMN 当季占比 double",
                        $"ALTER TABLE No6LeftJoin ADD COLUMN 上季占比 double",
                        $"ALTER TABLE No6LeftJoin ADD COLUMN 同比季度占比 double",
                        $"ALTER TABLE No6LeftJoin ADD COLUMN 本季度散户量 double",
                        $"ALTER TABLE No6LeftJoin ADD COLUMN 上季度散户量 double",
                        $"ALTER TABLE No6LeftJoin ADD COLUMN 同比季度散户量 double",
                        $"ALTER TABLE No6LeftJoin ADD COLUMN [本季度散户率(%)] double",
                        $"ALTER TABLE No6LeftJoin ADD COLUMN [上季度散户率(%)] double",
                        $"ALTER TABLE No6LeftJoin ADD COLUMN [同比季度散户率(%)] double",
                        $"ALTER TABLE No6LeftJoin ADD COLUMN 散户环比增量 double",
                        $"ALTER TABLE No6LeftJoin ADD COLUMN 散户同比增量 double",
                        $"ALTER TABLE No6LeftJoin ADD COLUMN [散户环比增速(%)] double",
                        $"ALTER TABLE No6LeftJoin ADD COLUMN [散户同比增速(%)] double",
                        };
                        foreach (string item in SqlsForAddSomeColumns)
                        {
                            adoForShowSingleMsg.ExecuteBySql(item);
                        }
                        string[] SqlsForUpdateColumns = new string[]
                        {
                             $"update  No6LeftJoin set [当季占比] = round ( [本季度{Indicator.Value}]/{SumOfIndicatorNow}*100,2)",
                            $"update  No6LeftJoin set [上季占比] = round ( [上季度{Indicator.Value}]/{SumOfIndicatorPre}*100,2)",
                            $"update  No6LeftJoin set [同比季度占比] = round ( [同比季度{Indicator.Value}]/{SumOfIndicatorYOY}*100,2)",
                            $"update  No6LeftJoin set [本季度散户量] = [本季度{Indicator.Value}] -[本季度规模户{IndicatorNameInMass}之和] where [本季度{Indicator.Value}] is not null",
                            $"update  No6LeftJoin set [上季度散户量] = [上季度{Indicator.Value}] -[上季度规模户{IndicatorNameInMass}之和]",
                            $"update  No6LeftJoin set [同比季度散户量] = [同比季度{Indicator.Value}] -[同比季度规模户{IndicatorNameInMass}之和]",
                            $"update  No6LeftJoin set [本季度散户率(%)] =Round( [本季度散户量] /[本季度{Indicator.Value}]*100,2) " +
                            $"where [本季度{Indicator.Value}] is not null and [本季度{Indicator.Value}] >0",
                            $"update  No6LeftJoin set [上季度散户率(%)] =Round( [上季度散户量] /[上季度{Indicator.Value}]*100,2) " +
                            $"where [上季度{Indicator.Value}] is not null and [上季度{Indicator.Value}] >0",
                            $"update  No6LeftJoin set [同比季度散户率(%)] =Round( [同比季度散户量] /[同比季度{Indicator.Value}]*100,2) " +
                            $"where [同比季度{Indicator.Value}] is not null and [同比季度{Indicator.Value}] >0",
                            $"update  No6LeftJoin set [散户环比增量] = [本季度散户量] -[上季度散户量]",//可能有问题
                            $"update  No6LeftJoin set [散户同比增量] = [本季度散户量] -[同比季度散户量]",
                        // $"update  No6LeftJoin set [散户环比增速(%)] = iif (上季度散户量 is null or 上季度散户量 = 0, iif(本季度散户量 is not null or 本季度散户量 > 0 ,100), round(([本季度散户量] /[上季度散户量] -1)*100,2))",
                           " UPDATE No6LeftJoin "+
"SET [散户环比增速(%)] = "+
    "CASE "+
       " WHEN 上季度散户量 IS NULL OR 上季度散户量 = 0 THEN "+
            "CASE "+
                "WHEN 本季度散户量 IS NOT NULL AND 本季度散户量 > 0 THEN 100 "+
                "ELSE 0  "+
            "END "+
        "ELSE "+
           " ROUND((CAST([本季度散户量] AS REAL) / 上季度散户量 - 1) * 100, 2) "+
    "END;"+
"    UPDATE No6LeftJoin"+
 " SET [散户同比增速(%)] = "+
  "  CASE" +
      "  WHEN 同比季度散户量 IS NULL OR 同比季度散户量 = 0 THEN "+
           " CASE" +
               " WHEN 本季度散户量 IS NOT NULL AND 本季度散户量 > 0 THEN 100 "+
              "  ELSE 0 "+
           " END"+
       " ELSE "+
           " ROUND((CAST([本季度散户量] AS REAL) / CAST(同比季度散户量 AS REAL) - 1) * 100, 2)"+
   " END;"

                       // $"update  No6LeftJoin set [散户同比增速(%)] = iif (同比季度散户量 is null or 同比季度散户量 = 0, iif(本季度散户量 is not null or 本季度散户量 > 0 ,100), round(([本季度散户量] /[同比季度散户量] -1)*100,2))"
                    //$"update  No6LeftJoin set [散户环比增速(%)] = iif (上季度散户量 is null or 上季度散户量 = 0, , round(([本季度散户量] /[上季度散户量] -1)*100,2))"
                        };
                        foreach (string item in SqlsForUpdateColumns)
                        {
                            adoForShowSingleMsg.ExecuteBySql(item);
                        }
                        DataTable dt = adoForShowSingleMsg.GetDataTable("select * from No6LeftJoin");
                        if (dt != null)
                        {
                            //dt.Columns.Add("本季度散户量", typeof(double));
                            //dt.Columns.Add("上季度散户量", typeof(double));
                            //dt.Columns.Add("同比季度散户量", typeof(double));
                            //dt.Columns.Add("本季度散户率", typeof(double));
                            //dt.Columns.Add("上季度散户率", typeof(double));
                            //dt.Columns.Add("同比季度散户率", typeof(double));
                            //dt.Columns.Add("散户环比增量", typeof(double));
                            //dt.Columns.Add("散户同比增量", typeof(double));
                            //dt.Columns.Add("散户环比增速", typeof(double));
                            //dt.Columns.Add("散户同比增速", typeof(double));
                            DataRow sumRow = dt.NewRow();
                            for (int i = 2; i < dt.Columns.Count; i++)
                            {
                                if (dt.Columns[i].ColumnName.Contains("%") == false)//如果不含有百分号,那么就是加起来就可以了
                                {
                                    var sum = dt.AsEnumerable().Sum(row =>
                                        Convert.ToDouble(row.Field<object>(i) ?? 0));
                                    sumRow[i] = sum;
                                }
                            }
                            sumRow[0] = "合计";
                            sumRow[1] = "合计";
                            if (!sumRow.IsNull("上季度" + Indicator.Value))
                            {
                                sumRow["环比增幅"] = Math.Round((double)sumRow["环比增量"] / (double)sumRow["上季度" + Indicator.Value] * 100, 2);
                            }
                            if (!sumRow.IsNull("同比季度" + Indicator.Value))
                            {
                                sumRow["同比增幅"] = Math.Round((double)sumRow["同比增量"] / (double)sumRow["同比季度" + Indicator.Value] * 100, 2);
                            }
                            sumRow["当季占比"] = 100d;
                            sumRow["上季占比"] = 100d;
                            sumRow["同比季度占比"] = 100d;
                            //散户率就不这么麻烦了
                            sumRow["本季度散户率(%)"] = Math.Round((double)sumRow["本季度散户量"] / (double)sumRow["本季度" + Indicator.Value] * 100, 2);
                            //上季度散户率
                            sumRow["上季度散户率(%)"] = Math.Round((double)sumRow["上季度散户量"] / (double)sumRow["上季度" + Indicator.Value] * 100, 2);
                            //同比季度散户率
                            sumRow["同比季度散户率(%)"] = Math.Round((double)sumRow["同比季度散户量"] / (double)sumRow["同比季度" + Indicator.Value] * 100, 2);
                            if (sumRow.IsNull($"上季度散户量") == false && (double)sumRow["上季度散户量"] > 0)
                            {
                                sumRow["散户环比增速(%)"] = Math.Round((double)sumRow["散户环比增量"] / (double)sumRow["上季度散户量"] * 100, 2);
                            }
                            if (sumRow.IsNull($"同比季度散户量") == false && (double)sumRow["同比季度散户量"] > 0)
                            {
                                sumRow["散户同比增速(%)"] = Math.Round((double)sumRow["散户同比增量"] / (double)sumRow["同比季度散户量"] * 100, 2);
                            }
                            dt.Rows.Add(sumRow);
                            return dt;
                        }

                    }
                    //如果没有跟规模户数据没有对应关系,此时只要显示同比环比即可
                    else
                    {
                        DataRow rowsum = adoForShowSingleMsg.GetDataTable($"select sum([本季度{Indicator.Value}]),sum([上季度{Indicator.Value}]),sum([同比季度{Indicator.Value}]) from No3LeftJoin").Rows[0];
                        double SumOfIndicatorNow = (double)rowsum[0];
                        double SumOfIndicatorPre = (double)rowsum[1];
                        double SumOfIndicatorYOY = (double)rowsum[2];
                        //计算占比
                        List<string> ListSqlsForAddColumnIntoNo3LeftJoin = new List<string>()
                {
                    $"alter table No3LeftJoin add column [当季占比(%)] double ",
                    $"alter table No3LeftJoin add column [上季度占比(%)] double ",
                    $"alter table No3LeftJoin add column [同比季度占比(%)] double ",
                };
                        if (SumOfIndicatorNow > 0)
                        {
                            ListSqlsForAddColumnIntoNo3LeftJoin.Add($"update  No3LeftJoin set [当季占比(%)] = round ( [本季度{Indicator.Value}]/{SumOfIndicatorNow}*100,2)");
                        }
                        if (SumOfIndicatorPre > 0)
                        {
                            ListSqlsForAddColumnIntoNo3LeftJoin.Add($"update  No3LeftJoin set [上季度占比(%)] = round ( [上季度{Indicator.Value}]/{SumOfIndicatorPre}*100,2)");
                        }
                        if (SumOfIndicatorYOY > 0)
                        {
                            ListSqlsForAddColumnIntoNo3LeftJoin.Add($"update  No3LeftJoin set [同比季度占比(%)] = round ( [同比季度{Indicator.Value}]/{SumOfIndicatorYOY}*100,2)");
                        }

                        foreach (var item in ListSqlsForAddColumnIntoNo3LeftJoin)
                        {
                            adoForShowSingleMsg.ExecuteBySql(item);
                        }
                        DataTable dt = adoForShowSingleMsg.GetDataTable($"select *  from No3LeftJoin");
                        DataRow SumRow = dt.NewRow();
                        SumRow[0] = "合计";

                        for (int i = 2; i < dt.Columns.Count; i++)
                        {
                            SumRow[i] = dt.AsEnumerable().Sum(row =>
                                Convert.ToDouble(row.Field<object>(i) ?? 0));
                            //把饼图需要的数据(就一个value)返回去

                        }
                        //double SumOfIndicatorNow = (from row in dt.AsEnumerable()
                        //                            select row.Field<double?>(2)).Sum().Value;
                        //var SumOfIndicatorPre = (from row in dt.AsEnumerable()
                        //                         select row.Field<double?>(3)).Sum().Value;
                        //var SumOfIndicatorYOY = (from row in dt.AsEnumerable()
                        //                         select row.Field<double?>(4)).Sum().Value;
                        SumRow[7] = Math.Round((SumOfIndicatorNow / SumOfIndicatorPre - 1) * 100, 2);
                        SumRow[8] = Math.Round((SumOfIndicatorNow / SumOfIndicatorYOY - 1) * 100, 2);
                        dt.Rows.Add(SumRow);

                        return dt;



                        //DataTable dt = adoForShowSingleMsg.GetDataTable($"select *  from No3LeftJoin");
                        //DataRow SumRow = dt.NewRow();
                        //SumRow[0] = SumRow[1] = "合计";

                        //for (int i = 2; i < dt.Columns.Count; i++)
                        //{
                        //    SumRow[i] = (from row in dt.AsEnumerable()
                        //                 select row.Field<double?>(i)).Sum().Value;
                        //    //把饼图需要的数据(就一个value)返回去

                        //}
                        //double SumOfIndicatorNow = (from row in dt.AsEnumerable()
                        //                            select row.Field<double?>(2)).Sum().Value;
                        //var SumOfIndicatorPre = (from row in dt.AsEnumerable()
                        //                         select row.Field<double?>(3)).Sum().Value;
                        //var SumOfIndicatorYOY = (from row in dt.AsEnumerable()
                        //                         select row.Field<double?>(4)).Sum().Value;
                        //SumRow[7] = Math.Round((SumOfIndicatorNow / SumOfIndicatorPre - 1) * 100, 2);
                        //SumRow[8] = Math.Round((SumOfIndicatorNow / SumOfIndicatorYOY - 1) * 100, 2);
                        //dt.Rows.Add(SumRow);

                        //return dt;
                    }
                    return null;





                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                StaticLogger.SystemError($"出现错误,错误信息{ex.Message},{Environment.NewLine}堆栈为{ex.StackTrace},{Environment.NewLine}对象为{ex.Source},{Environment.NewLine}方法为{ex.TargetSite}");
                return null;
            }
        }
        public DataTable GetAll406MsgThisSeason(KeyValuePair<string, string> CityItem, KeyValuePair<string, string> CountryItem, string WithYearSeasonA406Name)
        {
            //传入的表有可能在数据库中不存在
            try
            {
                string WithYearSeasonA209Name = Global.PlatformNow + Global.StrYearNow + Global.StrSeasonNow + "A209";
                string WithYearSeasonA210Name = Global.PlatformNow + Global.StrYearNow + Global.StrSeasonNow + "A210";
                string WithYearSeasonA211Name = Global.PlatformNow + Global.StrYearNow + Global.StrSeasonNow + "A211";
                string WithYearSeasonA212Name = Global.PlatformNow + Global.StrYearNow + Global.StrSeasonNow + "A212";
                MyDBOperator adoForShowA406Msg = MyDBOperator.DBOperatorInstance;
                DataTable DT = new DataTable();
                DataRow? RowOfA209Mass = null;
                DataRow? RowOfA210Mass = null;
                DataRow? RowOfA211Mass = null;
                DataRow? RowOfA212Mass = null;
                if (CityItem.Value.Equals("全部"))//全省的情况
                {
                    List<string> ListStrSumAllIndicators = new List<string>();
                    //编写部分求和数据
                    //for (int i = 0; i < ArrStrIndicatorOfA406.Length; i++)
                    //{
                    //    ListStrSumAllIndicators.Add($"sum([{ArrStrIndicatorOfA406[i]}]) as [{ArrStrIndicatorOfA406[i]}]");
                    //}
                    //string StrAllIndicatorsSumJoin = string.Join(",", ListStrSumAllIndicators);
                    string sqlToGetAllIndicatorsSum = $"select 市,市代码," +
                        $"SUM([生猪存栏头数（含未断奶小猪）01]) AS [生猪存栏头数（含未断奶小猪）01],SUM([能繁殖的母猪存栏头数02]) AS [能繁殖的母猪存栏头数02],SUM([生猪出栏头数03]) AS [生猪出栏头数03]," +
                        $"SUM([牛存栏头数04]) AS [牛存栏头数04],SUM([良种及改良种乳牛存栏头数05]) AS [良种及改良种乳牛存栏头数05],SUM([牛出栏头数06]) AS [牛出栏头数06]," +
                        $"SUM([羊存栏只数07]) AS [羊存栏只数07],SUM([羊出栏只数08]) AS [羊出栏只数08],SUM([家禽存栏只数09]) AS [家禽存栏只数09]," +
                        $"SUM([家禽出栏只数10]) AS [家禽出栏只数10],SUM([兔存栏只数11]) AS [兔存栏只数11],SUM([兔出栏只数12]) AS [兔出栏只数12]," +
                        $"SUM([肉类产量13]) AS [肉类产量13],SUM([猪肉产量（吨）14]) AS [猪肉产量（吨）14],SUM([牛肉产量（吨）15]) AS [牛肉产量（吨）15]," +
                        $"SUM([羊肉产量（吨）16]) AS [羊肉产量（吨）16],SUM([禽肉产量（吨）17]) AS [禽肉产量（吨）17],SUM([兔肉产量（吨）18]) AS [兔肉产量（吨）18]," +
                        $"SUM([其他肉产量（吨）19]) AS [其他肉产量（吨）19],SUM([鸡鸭鹅蛋产量（吨）20]) AS [鸡鸭鹅蛋产量（吨）20],SUM([生牛奶产量（吨）21]) AS [生牛奶产量（吨）21]," +
                        $"SUM([蚕茧产量（吨）22]) AS [蚕茧产量（吨）22],SUM([鸡存栏只数23]) AS [鸡存栏只数23],SUM([鸡出栏只数24]) AS [鸡出栏只数24] " +
                        $" FROM {WithYearSeasonA406Name} GROUP BY 市,市代码 order by 市代码";
                    //string sqlForGetAllSumIndicators = $"select 市,市代码, {StrAllIndicatorsSumJoin} from {WithYearSeasonA406Name} group by 市,市代码 order by 市代码";
                    //DT = adoForShowA406Msg.GetDataTable(sqlForGetAllSumIndicators);
                    DT = adoForShowA406Msg.GetDataTable(sqlToGetAllIndicatorsSum);
                    //获取406表中可以进行规模户联审的数据
                    //如果没有这个规模户的表,则不进行获取
                    if (adoForShowA406Msg.CheckIfTableExists(WithYearSeasonA209Name) == true)//如果本季度的209表存在与数据库中
                    {
                        string sqlForGetA209Mass = "select sum([期末存栏合计01]),sum ([能繁殖母猪存栏数06]),sum(当季出栏数)  from " + WithYearSeasonA209Name;
                        RowOfA209Mass = adoForShowA406Msg.GetDataTable(sqlForGetA209Mass).Rows[0];
                    }
                    if (adoForShowA406Msg.CheckIfTableExists(WithYearSeasonA210Name))
                    {
                        string sqlForGetA210Mass =
                            $"select sum([期末存栏头数01]),sum([奶牛存栏数04]),sum([当季出栏数]),sum([当季生牛奶产量（公斤）18]) from {WithYearSeasonA210Name}";
                        RowOfA210Mass = adoForShowA406Msg.GetDataTable(sqlForGetA210Mass).Rows[0];
                    }
                    if (adoForShowA406Msg.CheckIfTableExists(WithYearSeasonA211Name))
                    {
                        string sqlForGetA211Mass = "select sum([期末存栏只数01]),sum([当季出栏数]) from " + WithYearSeasonA211Name;
                        RowOfA211Mass = adoForShowA406Msg.GetDataTable(sqlForGetA211Mass).Rows[0];
                    }
                    if (adoForShowA406Msg.CheckIfTableExists(WithYearSeasonA212Name))
                    {
                        string sqlForGetA212Mass =
                            "select sum([期末存栏只数01]),sum([当季家禽出栏数]),sum([当季鸡存栏数]) as 鸡存栏,sum([当季鸡出栏数]) as 鸡出栏, sum([当季禽蛋产量（公斤）15])  from " +
                            WithYearSeasonA212Name;
                        RowOfA212Mass = adoForShowA406Msg.GetDataTable(sqlForGetA212Mass).Rows[0];
                    }

                }
                else if (CityItem.Value.Equals("全部") == false && CountryItem.Value.Equals("全部"))//某个市的分县的数据情况
                {
                    List<string> ListStrSumAllIndicators = new List<string>();
                    //编写部分求和数据
                    string sqlToGetAllIndicatorsSum = $"select 县,县代码," +
                       $"SUM([生猪存栏头数（含未断奶小猪）01]) AS [生猪存栏头数（含未断奶小猪）01],SUM([能繁殖的母猪存栏头数02]) AS [能繁殖的母猪存栏头数02],SUM([生猪出栏头数03]) AS [生猪出栏头数03]," +
                       $"SUM([牛存栏头数04]) AS [牛存栏头数04],SUM([良种及改良种乳牛存栏头数05]) AS [良种及改良种乳牛存栏头数05],SUM([牛出栏头数06]) AS [牛出栏头数06]," +
                       $"SUM([羊存栏只数07]) AS [羊存栏只数07],SUM([羊出栏只数08]) AS [羊出栏只数08],SUM([家禽存栏只数09]) AS [家禽存栏只数09]," +
                       $"SUM([家禽出栏只数10]) AS [家禽出栏只数10],SUM([兔存栏只数11]) AS [兔存栏只数11],SUM([兔出栏只数12]) AS [兔出栏只数12]," +
                       $"SUM([肉类产量13]) AS [肉类产量13],SUM([猪肉产量（吨）14]) AS [猪肉产量（吨）14],SUM([牛肉产量（吨）15]) AS [牛肉产量（吨）15]," +
                       $"SUM([羊肉产量（吨）16]) AS [羊肉产量（吨）16],SUM([禽肉产量（吨）17]) AS [禽肉产量（吨）17],SUM([兔肉产量（吨）18]) AS [兔肉产量（吨）18]," +
                       $"SUM([其他肉产量（吨）19]) AS [其他肉产量（吨）19],SUM([鸡鸭鹅蛋产量（吨）20]) AS [鸡鸭鹅蛋产量（吨）20],SUM([生牛奶产量（吨）21]) AS [生牛奶产量（吨）21]," +
                       $"SUM([蚕茧产量（吨）22]) AS [蚕茧产量（吨）22],SUM([鸡存栏只数23]) AS [鸡存栏只数23],SUM([鸡出栏只数24]) AS [鸡出栏只数24] " +
                       $" FROM {WithYearSeasonA406Name} GROUP BY 县,县代码 order by 县代码";
                    DT = adoForShowA406Msg.GetDataTable(sqlToGetAllIndicatorsSum);
                    //获取406表中可以进行规模户联审的数据
                    if (adoForShowA406Msg.CheckIfTableExists(WithYearSeasonA209Name))
                    {
                        string sqlForGetA209Mass =
                            $"select sum([期末存栏合计01]),sum ([能繁殖母猪存栏数06]),sum(当季出栏数)  from {WithYearSeasonA209Name} where 市代码 = '{CityItem.Key}'";
                        RowOfA209Mass = adoForShowA406Msg.GetDataTable(sqlForGetA209Mass).Rows[0];
                    }

                    if (adoForShowA406Msg.CheckIfTableExists(WithYearSeasonA210Name))
                    {
                        string sqlForGetA210Mass =
                            $"select sum([期末存栏头数01]),sum([奶牛存栏数04]),sum([当季出栏数]),sum([当季生牛奶产量（公斤）18]) from {WithYearSeasonA210Name} where 市代码  = '{CityItem.Key}'";
                        RowOfA210Mass = adoForShowA406Msg.GetDataTable(sqlForGetA210Mass).Rows[0];
                    }

                    if (adoForShowA406Msg.CheckIfTableExists(WithYearSeasonA211Name))
                    {
                        string sqlForGetA211Mass = $"select sum([期末存栏只数01]),sum([当季出栏数]) from {WithYearSeasonA211Name} where 市代码  = '{CityItem.Key}'";
                        RowOfA211Mass = adoForShowA406Msg.GetDataTable(sqlForGetA211Mass).Rows[0];
                    }

                    if (adoForShowA406Msg.CheckIfTableExists(WithYearSeasonA212Name))
                    {
                        string sqlForGetA212Mass = $"select sum([期末存栏只数01]) ,sum([当季家禽出栏数]),sum([当季鸡存栏数]) as 鸡存栏,sum([当季鸡出栏数]) as 鸡出栏, sum([当季禽蛋产量（公斤）15])  from {WithYearSeasonA212Name} where 市代码  = '{CityItem.Key}'";
                        RowOfA212Mass = adoForShowA406Msg.GetDataTable(sqlForGetA212Mass).Rows[0];
                    }


                }
                else if (CountryItem.Value.Equals("全部") == false) //某个县分乡镇的情况
                {

                    string SqlForGetAllIndicators =
                        "SELECT 乡,乡镇代码, [生猪存栏头数（含未断奶小猪）01],[能繁殖的母猪存栏头数02],[生猪出栏头数03],[牛存栏头数04],[良种及改良种乳牛存栏头数05]" +
                        ",[牛出栏头数06],[羊存栏只数07],[羊出栏只数08],[家禽存栏只数09],[家禽出栏只数10],[兔存栏只数11],[兔出栏只数12]" +
                        ",[肉类产量13],[猪肉产量（吨）14],[牛肉产量（吨）15],[羊肉产量（吨）16],[禽肉产量（吨）17],[兔肉产量（吨）18],[其他肉产量（吨）19]" +
                        ",[鸡鸭鹅蛋产量（吨）20],[生牛奶产量（吨）21],[蚕茧产量（吨）22],[鸡存栏只数23],[鸡出栏只数24] " +
                        $"FROM {WithYearSeasonA406Name} WHERE 县代码 = '{CountryItem.Key}'";
                    DT = adoForShowA406Msg.GetDataTable(SqlForGetAllIndicators);
                    if (adoForShowA406Msg.CheckIfTableExists(WithYearSeasonA209Name))
                    {
                        string sqlForGetA209Mass2 =
                            $"select sum([期末存栏合计01]),sum ([能繁殖母猪存栏数06]),sum(当季出栏数)  from {WithYearSeasonA209Name} where 县代码 = '{CountryItem.Key}'";
                        RowOfA209Mass = adoForShowA406Msg.GetDataTable(sqlForGetA209Mass2).Rows[0];
                    }

                    if (adoForShowA406Msg.CheckIfTableExists(WithYearSeasonA210Name))
                    {
                        string sqlForGetA210Mass2 =
                            $"select sum([期末存栏头数01]),sum([奶牛存栏数04]),sum([当季出栏数]),sum([当季生牛奶产量（公斤）18]) from {WithYearSeasonA210Name} where 县代码  = '{CountryItem.Key}'";
                        RowOfA210Mass = adoForShowA406Msg.GetDataTable(sqlForGetA210Mass2).Rows[0];
                    }

                    if (adoForShowA406Msg.CheckIfTableExists(WithYearSeasonA211Name))
                    {
                        string sqlForGetA211Mass2 =
                            $"select sum([期末存栏只数01]),sum([当季出栏数]) from {WithYearSeasonA211Name} where 县代码  = '{CountryItem.Key}'";
                        RowOfA211Mass = adoForShowA406Msg.GetDataTable(sqlForGetA211Mass2).Rows[0];
                    }

                    if (adoForShowA406Msg.CheckIfTableExists(WithYearSeasonA212Name))
                    {
                        string sqlForGetA212Mass2 =
                            $"select sum([期末存栏只数01]),sum([当季家禽出栏数]),sum([当季鸡存栏数]) as 鸡存栏,sum([当季鸡出栏数]) as 鸡出栏, sum([当季禽蛋产量（公斤）15])  from {WithYearSeasonA212Name} where 县代码  = '{CountryItem.Key}'";
                        RowOfA212Mass = adoForShowA406Msg.GetDataTable(sqlForGetA212Mass2).Rows[0];
                    }
                }

                DataRow SumRow = DT.NewRow();
                //SumRow[0] = (SumRow[1] = "合计");
                SumRow[0] = "合计";
                for (int i = 2; i < DT.Columns.Count; i++)
                {
                    double sum = DT.AsEnumerable().Sum(row =>
                        Convert.ToDouble(row.Field<object>(i) ?? 0));
                    SumRow[i] = sum;

                }
                DT.Rows.Add(SumRow);
                DataRow MassRow = DT.NewRow();
                //DataRow[] Datarows = { RowOfA209Mass, RowOfA210Mass, RowOfA211Mass, RowOfA212Mass };
                //foreach (DataRow item in Datarows)
                //{
                //    for (int i = 0; i < item.ItemArray.Length; i++)
                //    {
                //        if (item[i] == DBNull.Value)
                //        {
                //            item[i] = 0.0; // 将 DBNull 值转换为 double 类型的 0
                //        }
                //    }
                //}
                MassRow[0] = "规模户数据";
                MassRow[2] = RowOfA209Mass == null ? 0 : RowOfA209Mass.IsNull(0) ? 0 : RowOfA209Mass[0];
                MassRow[3] = RowOfA209Mass == null ? 0 : RowOfA209Mass.IsNull(1) ? 0 : RowOfA209Mass[1];
                MassRow[4] = RowOfA209Mass == null ? 0 : RowOfA209Mass.IsNull(2) ? 0 : RowOfA209Mass[2];
                MassRow[5] = RowOfA210Mass == null ? 0 : RowOfA210Mass.IsNull(0) ? 0 : RowOfA210Mass[0];
                MassRow[6] = RowOfA210Mass == null ? 0 : RowOfA210Mass.IsNull(1) ? 0 : RowOfA210Mass[1];
                MassRow[7] = RowOfA210Mass == null ? 0 : RowOfA210Mass.IsNull(2) ? 0 : RowOfA210Mass[2];
                //MessageBox.Show(RowOfA210Mass[3].GetType().Name);
                MassRow[22] = RowOfA210Mass == null ? 0 : (double)(RowOfA210Mass.IsNull(3) ? 0 : RowOfA210Mass[3]) / 1000.0;
                MassRow[8] = RowOfA211Mass == null ? 0 : RowOfA211Mass.IsNull(0) ? 0 : RowOfA211Mass[0];
                MassRow[9] = RowOfA211Mass == null ? 0 : RowOfA211Mass.IsNull(1) ? 0 : RowOfA211Mass[1];
                MassRow[10] = RowOfA212Mass == null ? 0 : RowOfA212Mass.IsNull(0) ? 0 : RowOfA212Mass[0];
                MassRow[11] = RowOfA212Mass == null ? 0 : RowOfA212Mass.IsNull(1) ? 0 : RowOfA212Mass[1];
                MassRow[24] = RowOfA212Mass == null ? 0 : RowOfA212Mass.IsNull(2) ? 0 : RowOfA212Mass[2];
                MassRow[25] = RowOfA212Mass == null ? 0 : RowOfA212Mass.IsNull(3) ? 0 : RowOfA212Mass[3];
                MassRow[21] = RowOfA212Mass == null ? 0 : (double)(RowOfA212Mass.IsNull(4) ? 0 : RowOfA212Mass[4]) / 1000.0;
                DT.Rows.Add(MassRow);
                DataRow Row散户 = DT.NewRow();
                DataRow Row散户比例 = DT.NewRow();
                Row散户[0] = "散户量";
                Row散户比例[0] = "散户率(%)";
                for (int i = 2; i < DT.Columns.Count; i++)
                {
                    if (MassRow.IsNull(i) == false)
                    {
                        double sumofAll = DT.AsEnumerable().Sum(row =>
                            Convert.ToDouble(row.Field<object>(i) ?? 0));
                        Row散户[i] = sumofAll - (double)MassRow[i];
                        Row散户比例[i] = Math.Round((sumofAll - (double)MassRow[i]) / sumofAll * 100, 3);
                    }
                }
                DT.Rows.Add(Row散户);
                DT.Rows.Add(Row散户比例);
                return DT;
            }
            catch (Exception ex)
            {
                StaticLogger.SystemError($"出现错误,错误信息{ex.Message},{Environment.NewLine}堆栈为{ex.StackTrace},{Environment.NewLine}对象为{ex.Source},{Environment.NewLine}方法为{ex.TargetSite}");
                throw;
            }
        }

        internal DataTable GetAll406MsgThisYear(KeyValuePair<string, string> selectedCity, KeyValuePair<string, string> selectedCountry, IEnumerable<string> allTables, int 期别季, IProgress<int> progress)
        {
            try
            {
                MyDBOperator getSimpleAddOperator = MyDBOperator.DBOperatorInstance;
                var 所有包含A406的元素组成的新数组 = allTables.Where(x => x.Contains("A406")).ToArray();
                var 所有包含A209的元素组成的新数组 = allTables.Where(x => x.Contains("A209")).ToArray();
                var 所有包含A210的元素组成的新数组 = allTables.Where(x => x.Contains("A210")).ToArray();
                var 所有包含A211的元素组成的新数组 = allTables.Where(x => x.Contains("A211")).ToArray();
                var 所有包含A212的元素组成的新数组 = allTables.Where(x => x.Contains("A212")).ToArray();
                string sqlForGetA209Mass = null;
                string sqlForGetA210Mass = null;
                string sqlForGetA211Mass = null;
                string sqlForGetA212Mass = null;
                DataTable DT = null;
                DataRow RowOfA209Mass = null;
                DataRow RowOfA210Mass = null;
                DataRow RowOfA211Mass = null;
                DataRow RowOfA212Mass = null;

                //为进度条准备
                int processedFiles = 0;
                int totalFiles = 5;
                int percentage = 0;
                if (所有包含A209的元素组成的新数组.Length > 0)
                {
                    getSimpleAddOperator.MergeTablesFromDatabaseToAMergeTableInDB(所有包含A209的元素组成的新数组, "本年度A209表简单拼接");
                }
                else
                {
                    getSimpleAddOperator.CreateOnlyColumnsNewTable("A209模板", "本年度A209表简单拼接");
                }
               
                processedFiles++;
                percentage = (int)((double)processedFiles / totalFiles * 100);
                progress.Report(percentage);
                if (所有包含A210的元素组成的新数组.Length > 0)
                {
                    getSimpleAddOperator.MergeTablesFromDatabaseToAMergeTableInDB(所有包含A210的元素组成的新数组, "本年度A210表简单拼接");
                }
                else
                {
                    getSimpleAddOperator.CreateOnlyColumnsNewTable("A210模板", "本年度A210表简单拼接");
                }

                processedFiles++;
                percentage = (int)((double)processedFiles / totalFiles * 100);
                progress.Report(percentage);
                if (所有包含A211的元素组成的新数组.Length > 0)
                {
                    getSimpleAddOperator.MergeTablesFromDatabaseToAMergeTableInDB(所有包含A211的元素组成的新数组, "本年度A211表简单拼接");
                }
                else
                {
                    getSimpleAddOperator.CreateOnlyColumnsNewTable("A211模板", "本年度A211表简单拼接");
                }

                processedFiles++;
                percentage = (int)((double)processedFiles / totalFiles * 100);
                progress.Report(percentage);
                if (所有包含A212的元素组成的新数组.Length > 0)
                {
                    getSimpleAddOperator.MergeTablesFromDatabaseToAMergeTableInDB(所有包含A212的元素组成的新数组, "本年度A212表简单拼接");
                }
                else
                {
                    getSimpleAddOperator.CreateOnlyColumnsNewTable("A212模板", "本年度A212表简单拼接");
                }

                processedFiles++;
                percentage = (int)((double)processedFiles / totalFiles * 100);
                progress.Report(percentage);
                getSimpleAddOperator.MergeTablesFromDatabaseToAMergeTableInDB(所有包含A406的元素组成的新数组, "本年度A406表简单拼接");
                processedFiles++;
                percentage = (int)((double)processedFiles / totalFiles * 100);
                progress.Report(percentage);
                string sqlToGetA406Add = null;
                if (selectedCity.Value.Equals("全部"))//全省的分地市展示
                {
                    sqlToGetA406Add = $"  WITH 存栏合计 AS(SELECT 市, 市代码, SUM([生猪存栏头数（含未断奶小猪）01]) AS [生猪存栏头数（含未断奶小猪）01]" +
                        $", SUM([能繁殖的母猪存栏头数02]) as [能繁殖的母猪存栏头数02], " +
                         "  SUM([牛存栏头数04]) as [牛存栏头数04], SUM(良种及改良种乳牛存栏头数05) AS 良种及改良种乳牛存栏头数05, " +
                         "SUM([羊存栏只数07]) as [羊存栏只数07],SUM([家禽存栏只数09]) as [家禽存栏只数09],SUM([兔存栏只数11]) as [兔存栏只数11]" +

                           $"FROM 本年度A406表简单拼接  WHERE 期别季 = '{期别季}' " +
                          " GROUP BY 市,市代码), " +
                       " 出栏合计 AS(SELECT 市,市代码,sum([生猪出栏头数03]) as [生猪出栏头数03], SUM([牛出栏头数06]) as [牛出栏头数06], SUM(羊出栏只数08) as 羊出栏只数08" +
                       ", SUM(家禽出栏只数10) as 家禽出栏只数10,  sum(兔出栏只数12) as 兔出栏只数12, " +
                         "   sum(肉类产量13) as 肉类产量13,  sum([猪肉产量（吨）14]) as [猪肉产量（吨）14],  sum([牛肉产量（吨）15]) as [牛肉产量（吨）15], " +

                           " sum([羊肉产量（吨）16]) as [羊肉产量（吨）16],   sum([禽肉产量（吨）17]) as [禽肉产量（吨）17], " +
                           " sum([兔肉产量（吨）18]) as [兔肉产量（吨）18], sum([其他肉产量（吨）19]) as [其他肉产量（吨）19],sum([鸡鸭鹅蛋产量（吨）20]) as [鸡鸭鹅蛋产量（吨）20]," +
                           " sum([生牛奶产量（吨）21]) as [生牛奶产量（吨）21], sum([蚕茧产量（吨）22]) as [蚕茧产量（吨）22],sum([鸡存栏只数23]) as [鸡存栏只数23],sum([鸡出栏只数24]) as [鸡出栏只数24]" +
                           " FROM 本年度A406表简单拼接 " +
                           " GROUP BY 市,市代码 )" +
                        "SELECT COALESCE(c.市, o.市) AS 市, COALESCE(c.市代码, o.市代码) AS 市代码, " +
                           " COALESCE(c.[生猪存栏头数（含未断奶小猪）01], 0) AS [生猪存栏头数（含未断奶小猪）01], COALESCE(c.[能繁殖的母猪存栏头数02], 0) as [能繁殖的母猪存栏头数02], " +
                           " COALESCE(o.[生猪出栏头数03], 0) as [生猪出栏头数03],COALESCE(c.[牛存栏头数04], 0) as [牛存栏头数04], " +
                           " COALESCE(c.[良种及改良种乳牛存栏头数05], 0) as [良种及改良种乳牛存栏头数05], " +
                           "COALESCE(o.[牛出栏头数06], 0) as [牛出栏头数06]," +
                           "COALESCE(c.[羊存栏只数07], 0) as [羊存栏只数07]," +
                           " COALESCE(o.[羊出栏只数08], 0) as [羊出栏只数08],   COALESCE(c.家禽存栏只数09, 0) AS 家禽存栏只数09, " +
                           " COALESCE(o.家禽出栏只数10, 0) AS 家禽出栏只数10, COALESCE(c.兔存栏只数11, 0) AS 兔存栏只数11, COALESCE(o.兔出栏只数12, 0) AS 兔出栏只数12, " +
                           " COALESCE(o.肉类产量13, 0) AS 肉类产量13, COALESCE(o.[猪肉产量（吨）14], 0) AS [猪肉产量（吨）14], " +
                            "COALESCE(o.[牛肉产量（吨）15], 0) AS [牛肉产量（吨）15],COALESCE(o.[羊肉产量（吨）16], 0) AS [羊肉产量（吨）16], " +
                           " COALESCE(o.[禽肉产量（吨）17], 0) AS [禽肉产量（吨）17],COALESCE(o.[兔肉产量（吨）18], 0) AS [兔肉产量（吨）18], " +
                           " COALESCE(o.[其他肉产量（吨）19], 0) AS [其他肉产量（吨）19],  COALESCE(o.[鸡鸭鹅蛋产量（吨）20], 0) AS [鸡鸭鹅蛋产量（吨）20], " +
                           "COALESCE(o.[生牛奶产量（吨）21], 0) AS [生牛奶产量（吨）21],  COALESCE(o.[蚕茧产量（吨）22], 0) AS [蚕茧产量（吨）22], " +
                           "COALESCE(o.[鸡存栏只数23], 0) AS [鸡存栏只数23],  COALESCE(o.[鸡出栏只数24], 0) AS [鸡出栏只数24] " +
                       " FROM " +
                           " 存栏合计 c " +
                       " FULL OUTER JOIN " +
                           " 出栏合计 o ON c.市 = o.市 and c.市代码 = o.市代码" +
                       " ORDER BY  市代码; ";


                    //获取406表中可以进行规模户联审的数据
                    sqlForGetA209Mass =
                 $"  WITH 存栏合计 AS(SELECT 市, 市代码, sum([期末存栏合计01])  AS [期末存栏合计01], SUM([能繁殖母猪存栏数06]) AS [能繁殖母猪存栏数06] " +

                           $" FROM 本年度A209表简单拼接  WHERE 期别季 = '{期别季}' " +
                          " GROUP BY 市,市代码), " +
                       " 出栏合计 AS(SELECT 市,市代码, SUM(当季出栏数) AS 当季出栏数" +
                           " FROM 本年度A209表简单拼接 " +
                           " GROUP BY 市,市代码 )" +
                        "SELECT COALESCE(c.市, o.市) AS 市, COALESCE(c.市代码, o.市代码) AS 市代码, " +
                           " COALESCE(c.[期末存栏合计01], 0) AS [期末存栏合计01], COALESCE(c.[能繁殖母猪存栏数06], 0) as [能繁殖母猪存栏数06], " +
                           " COALESCE(o.[当季出栏数], 0) as [当季出栏数] " +
                       " FROM " +
                           " 存栏合计 c " +
                       " FULL OUTER JOIN " +
                           " 出栏合计 o ON c.市 = o.市 and c.市代码 = o.市代码" +
                       " ORDER BY  市代码; ";


                    sqlForGetA210Mass =
                        $"  WITH 存栏合计 AS(SELECT 市, 市代码, sum([期末存栏头数01])  AS [期末存栏头数01], SUM([奶牛存栏数04]) AS [奶牛存栏数04] " +

                           $" FROM 本年度A210表简单拼接  WHERE 期别季 = '{期别季}' " +
                          " GROUP BY 市,市代码), " +
                       " 出栏合计 AS(SELECT 市,市代码, SUM(当季出栏数) AS 当季出栏数,sum([当季生牛奶产量（公斤）18]) as [当季生牛奶产量（公斤）18] " +
                           " FROM 本年度A210表简单拼接 " +
                           " GROUP BY 市,市代码 )" +
                        "SELECT COALESCE(c.市, o.市) AS 市, COALESCE(c.市代码, o.市代码) AS 市代码, " +
                           " COALESCE(c.[期末存栏头数01], 0) AS [期末存栏头数01], COALESCE(c.[奶牛存栏数04], 0) as [奶牛存栏数04], " +
                           " COALESCE(o.[当季出栏数], 0) as [当季出栏数],COALESCE(o.[当季生牛奶产量（公斤）18], 0) as [当季生牛奶产量（公斤）18] " +
                       " FROM " +
                           " 存栏合计 c " +
                       " FULL OUTER JOIN " +
                           " 出栏合计 o ON c.市 = o.市 and c.市代码 = o.市代码" +
                       " ORDER BY  市代码; ";





                    sqlForGetA211Mass =
                                      $"  WITH 存栏合计 AS(SELECT 市, 市代码, sum([期末存栏只数01])  AS [期末存栏只数01] " +

                           $" FROM 本年度A211表简单拼接  WHERE 期别季 = '{期别季}' " +
                          " GROUP BY 市,市代码), " +
                       " 出栏合计 AS(SELECT 市,市代码, SUM(当季出栏数) AS 当季出栏数 " +
                           " FROM 本年度A211表简单拼接 " +
                           " GROUP BY 市,市代码 )" +
                        "SELECT COALESCE(c.市, o.市) AS 市, COALESCE(c.市代码, o.市代码) AS 市代码, " +
                           " COALESCE(c.[期末存栏只数01], 0) AS [期末存栏只数01], " +
                           " COALESCE(o.[当季出栏数], 0) as [当季出栏数]  " +
                       " FROM " +
                           " 存栏合计 c " +
                       " FULL OUTER JOIN " +
                           " 出栏合计 o ON c.市 = o.市 and c.市代码 = o.市代码" +
                       " ORDER BY  市代码; ";






                    sqlForGetA212Mass =
                         $"  WITH 存栏合计 AS(SELECT 市, 市代码, sum([期末存栏只数01])  AS [期末存栏只数01] " +

                           $" FROM 本年度A212表简单拼接  WHERE 期别季 = '{期别季}' " +
                          " GROUP BY 市,市代码), " +
                       " 出栏合计 AS(SELECT 市,市代码, SUM(当季家禽出栏数) AS 当季家禽出栏数,sum([当季鸡存栏数]) as 鸡存栏,sum([当季鸡出栏数]) as 鸡出栏, sum([当季禽蛋产量（公斤）15]) as 禽蛋产量 " +
                           " FROM 本年度A212表简单拼接 " +
                           " GROUP BY 市,市代码 )" +
                        "SELECT COALESCE(c.市, o.市) AS 市, COALESCE(c.市代码, o.市代码) AS 市代码, " +
                           " COALESCE(c.[期末存栏只数01], 0) AS [期末存栏只数01], " +
                           " COALESCE(o.[当季家禽出栏数], 0) as [当季家禽出栏数],  " +
                           " COALESCE(o.[鸡存栏], 0) as [鸡存栏],  " +
                           " COALESCE(o.[鸡出栏], 0) as [鸡出栏],  " +
                           " COALESCE(o.[禽蛋产量], 0) as [禽蛋产量]  " +
                       " FROM " +
                           " 存栏合计 c " +
                       " FULL OUTER JOIN " +
                           " 出栏合计 o ON c.市 = o.市 and c.市代码 = o.市代码" +
                       " ORDER BY  市代码; ";





                }
                else if (selectedCountry.Value.Equals("全部"))//某个市的
                {
                    sqlToGetA406Add = $"  WITH 存栏合计 AS(SELECT 县, 县代码, SUM([生猪存栏头数（含未断奶小猪）01]) AS [生猪存栏头数（含未断奶小猪）01]" +
                        $", SUM([能繁殖的母猪存栏头数02]) as [能繁殖的母猪存栏头数02], " +
                         "  SUM([牛存栏头数04]) as [牛存栏头数04], SUM(良种及改良种乳牛存栏头数05) AS 良种及改良种乳牛存栏头数05, " +
                         "SUM([羊存栏只数07]) as [羊存栏只数07],SUM([家禽存栏只数09]) as [家禽存栏只数09],SUM([兔存栏只数11]) as [兔存栏只数11]" +

                           $"FROM 本年度A406表简单拼接  WHERE 期别季 = '{期别季}' and 市代码 = '{selectedCity.Key}'  " +
                          " GROUP BY 县,县代码), " +
                       " 出栏合计 AS(SELECT 县,县代码,sum([生猪出栏头数03]) as [生猪出栏头数03], SUM([牛出栏头数06]) as [牛出栏头数06], SUM(羊出栏只数08) as 羊出栏只数08" +
                       ", SUM(家禽出栏只数10) as 家禽出栏只数10,  sum(兔出栏只数12) as 兔出栏只数12, " +
                         "   sum(肉类产量13) as 肉类产量13,  sum([猪肉产量（吨）14]) as [猪肉产量（吨）14],  sum([牛肉产量（吨）15]) as [牛肉产量（吨）15], " +
                            "sum([羊肉产量（吨）16]) as [羊肉产量（吨）16],   sum([禽肉产量（吨）17]) as [禽肉产量（吨）17], " +
                           " sum([兔肉产量（吨）18]) as [兔肉产量（吨）18], sum([其他肉产量（吨）19]) as [其他肉产量（吨）19],sum([鸡鸭鹅蛋产量（吨）20]) as [鸡鸭鹅蛋产量（吨）20]," +
                           " sum([生牛奶产量（吨）21]) as [生牛奶产量（吨）21], sum([蚕茧产量（吨）22]) as [蚕茧产量（吨）22],sum([鸡存栏只数23]) as [鸡存栏只数23],sum([鸡出栏只数24]) as [鸡出栏只数24]" +
                           " FROM 本年度A406表简单拼接 " +
                           " GROUP BY 县,县代码 )" +
                        "SELECT COALESCE(c.县, o.县) AS 县, COALESCE(c.县代码, o.县代码) AS 县代码, " +
                           " COALESCE(c.[生猪存栏头数（含未断奶小猪）01], 0) AS [生猪存栏头数（含未断奶小猪）01], COALESCE(c.[能繁殖的母猪存栏头数02], 0) as [能繁殖的母猪存栏头数02], " +
                           " COALESCE(o.[生猪出栏头数03], 0) as [生猪出栏头数03],COALESCE(c.[牛存栏头数04], 0) as [牛存栏头数04], " +
                           " COALESCE(c.[良种及改良种乳牛存栏头数05], 0) as [良种及改良种乳牛存栏头数05], " +
                           "COALESCE(o.[牛出栏头数06], 0) as [牛出栏头数06]," +
                           "COALESCE(c.[羊存栏只数07], 0) as [羊存栏只数07]," +
                           " COALESCE(o.[羊出栏只数08], 0) as [羊出栏只数08],   COALESCE(c.家禽存栏只数09, 0) AS 家禽存栏只数09, " +
                           " COALESCE(o.家禽出栏只数10, 0) AS 家禽出栏只数10, COALESCE(c.兔存栏只数11, 0) AS 兔存栏只数11, COALESCE(o.兔出栏只数12, 0) AS 兔出栏只数12, " +
                           " COALESCE(o.肉类产量13, 0) AS 肉类产量13, COALESCE(o.[猪肉产量（吨）14], 0) AS [猪肉产量（吨）14], " +
                            "COALESCE(o.[牛肉产量（吨）15], 0) AS [牛肉产量（吨）15],COALESCE(o.[羊肉产量（吨）16], 0) AS [羊肉产量（吨）16], " +
                           " COALESCE(o.[禽肉产量（吨）17], 0) AS [禽肉产量（吨）17],COALESCE(o.[兔肉产量（吨）18], 0) AS [兔肉产量（吨）18], " +
                           " COALESCE(o.[其他肉产量（吨）19], 0) AS [其他肉产量（吨）19],  COALESCE(o.[鸡鸭鹅蛋产量（吨）20], 0) AS [鸡鸭鹅蛋产量（吨）20], " +
                           "COALESCE(o.[生牛奶产量（吨）21], 0) AS [生牛奶产量（吨）21],  COALESCE(o.[蚕茧产量（吨）22], 0) AS [蚕茧产量（吨）22], " +
                           "COALESCE(o.[鸡存栏只数23], 0) AS [鸡存栏只数23],  COALESCE(o.[鸡出栏只数24], 0) AS [鸡出栏只数24] " +
                       " FROM " +
                           " 存栏合计 c " +
                       " FULL OUTER JOIN " +
                           " 出栏合计 o ON c.县 = o.县 and c.县代码 = o.县代码" +
                       " ORDER BY  县代码; ";
                    //获取406表中可以进行规模户联审的数据
                    //   sqlForGetA209Mass =
                    //$"  WITH 存栏合计 AS(SELECT 县, 县代码, sum([期末存栏合计01])  AS [期末存栏合计01], SUM([能繁殖母猪存栏数06]) AS [能繁殖母猪存栏数06] " +

                    //          $" FROM 本年度A209表简单拼接  WHERE 期别季 = '{期别季}' and 市代码 = '{selectedCity.Key}' " +
                    //         " GROUP BY 县,县代码), " +
                    //      " 出栏合计 AS(SELECT 县,县代码, SUM(当季出栏数) AS 当季出栏数" +
                    //          " FROM 本年度A209表简单拼接 " +
                    //          " GROUP BY 县,县代码 )" +
                    //       "SELECT COALESCE(c.县, o.县) AS 县, COALESCE(c.县代码, o.县代码) AS 县代码, " +
                    //          " COALESCE(c.[期末存栏合计01], 0) AS [期末存栏合计01], COALESCE(c.[能繁殖母猪存栏数06], 0) as [能繁殖母猪存栏数06], " +
                    //          " COALESCE(o.[当季出栏数], 0) as [当季出栏数] " +
                    //      " FROM " +
                    //          " 存栏合计 c " +
                    //      " FULL OUTER JOIN " +
                    //          " 出栏合计 o ON c.县 = o.县 and c.县代码 = o.县代码" +
                    //      " ORDER BY  县代码; ";


                    //   sqlForGetA210Mass =
                    //       $"  WITH 存栏合计 AS(SELECT 县, 县代码, sum([期末存栏头数01])  AS [期末存栏头数01], SUM([奶牛存栏数04]) AS [奶牛存栏数04] " +

                    //          $" FROM 本年度A210表简单拼接  WHERE 期别季 = '{期别季}' and 市代码 = '{selectedCity.Key}' " +
                    //         " GROUP BY 县,县代码), " +
                    //      " 出栏合计 AS(SELECT 县,县代码, SUM(当季出栏数) AS 当季出栏数,sum([当季生牛奶产量（公斤）18]) as [当季生牛奶产量（公斤）18] " +
                    //          " FROM 本年度A210表简单拼接 " +
                    //          " GROUP BY 县,县代码 )" +
                    //       "SELECT COALESCE(c.县, o.县) AS 县, COALESCE(c.县代码, o.县代码) AS 县代码, " +
                    //          " COALESCE(c.[期末存栏头数01], 0) AS [期末存栏头数01], COALESCE(c.[奶牛存栏数04], 0) as [奶牛存栏数04], " +
                    //          " COALESCE(o.[当季出栏数], 0) as [当季出栏数],COALESCE(o.[当季生牛奶产量（公斤）18], 0) as [当季生牛奶产量（公斤）18] " +
                    //      " FROM " +
                    //          " 存栏合计 c " +
                    //      " FULL OUTER JOIN " +
                    //          " 出栏合计 o ON c.县 = o.县 and c.县代码 = o.县代码" +
                    //      " ORDER BY  县代码; ";





                    //   sqlForGetA211Mass =
                    //                     $"  WITH 存栏合计 AS(SELECT 县, 县代码, sum([期末存栏只数01])  AS [期末存栏只数01] " +

                    //          $" FROM 本年度A211表简单拼接  WHERE 期别季 = '{期别季}' and 市代码 = '{selectedCity.Key}' " +
                    //         " GROUP BY 县,县代码), " +
                    //      " 出栏合计 AS(SELECT 县,县代码, SUM(当季出栏数) AS 当季出栏数 " +
                    //          " FROM 本年度A211表简单拼接 " +
                    //          " GROUP BY 县,县代码 )" +
                    //       "SELECT COALESCE(c.县, o.县) AS 县, COALESCE(c.县代码, o.县代码) AS 县代码, " +
                    //          " COALESCE(c.[期末存栏只数01], 0) AS [期末存栏只数01], " +
                    //          " COALESCE(o.[当季出栏数], 0) as [当季出栏数]  " +
                    //      " FROM " +
                    //          " 存栏合计 c " +
                    //      " FULL OUTER JOIN " +
                    //          " 出栏合计 o ON c.县 = o.县 and c.县代码 = o.县代码" +
                    //      " ORDER BY  县代码; ";






                    //   sqlForGetA212Mass =
                    //        $"  WITH 存栏合计 AS(SELECT 县, 县代码, sum([期末存栏只数01])  AS [期末存栏只数01] " +

                    //          $" FROM 本年度A212表简单拼接  WHERE 期别季 = '{期别季}' and 市代码 = '{selectedCity.Key}' " +
                    //         " GROUP BY 县,县代码), " +
                    //      " 出栏合计 AS(SELECT 县,县代码, SUM(当季家禽出栏数) AS 当季家禽出栏数,sum([当季鸡存栏数]) as 鸡存栏,sum([当季鸡出栏数]) as 鸡出栏, sum([当季禽蛋产量（公斤）15]) as 禽蛋产量 " +
                    //          " FROM 本年度A212表简单拼接 " +
                    //          " GROUP BY 县,县代码 )" +
                    //       "SELECT COALESCE(c.县, o.县) AS 县, COALESCE(c.县代码, o.县代码) AS 县代码, " +
                    //          " COALESCE(c.[期末存栏只数01], 0) AS [期末存栏只数01], " +
                    //          " COALESCE(o.[当季家禽出栏数], 0) as [当季家禽出栏数],  " +
                    //          " COALESCE(o.[鸡存栏], 0) as [鸡存栏],  " +
                    //          " COALESCE(o.[鸡出栏], 0) as [鸡出栏],  " +
                    //          " COALESCE(o.[禽蛋产量], 0) as [禽蛋产量]  " +
                    //      " FROM " +
                    //          " 存栏合计 c " +
                    //      " FULL OUTER JOIN " +
                    //          " 出栏合计 o ON c.县 = o.县 and c.县代码 = o.县代码" +
                    //      " ORDER BY  县代码; ";

                    sqlForGetA209Mass =
                $"  WITH 存栏合计 AS(SELECT 市, 市代码, sum([期末存栏合计01])  AS [期末存栏合计01], SUM([能繁殖母猪存栏数06]) AS [能繁殖母猪存栏数06] " +

                          $" FROM 本年度A209表简单拼接  WHERE 期别季 = '{期别季}' and 市代码 = '{selectedCity.Key}' " +
                         " GROUP BY 市,市代码), " +
                      " 出栏合计 AS(SELECT 市,市代码, SUM(当季出栏数) AS 当季出栏数" +
                          " FROM 本年度A209表简单拼接 " +
                          " GROUP BY 市,市代码 )" +
                       "SELECT COALESCE(c.市, o.市) AS 市, COALESCE(c.市代码, o.市代码) AS 市代码, " +
                          " COALESCE(c.[期末存栏合计01], 0) AS [期末存栏合计01], COALESCE(c.[能繁殖母猪存栏数06], 0) as [能繁殖母猪存栏数06], " +
                          " COALESCE(o.[当季出栏数], 0) as [当季出栏数] " +
                      " FROM " +
                          " 存栏合计 c " +
                      " FULL OUTER JOIN " +
                          " 出栏合计 o ON c.市 = o.市 and c.市代码 = o.市代码" +
                      " ORDER BY  市代码; ";


                    sqlForGetA210Mass =
                        $"  WITH 存栏合计 AS(SELECT 市, 市代码, sum([期末存栏头数01])  AS [期末存栏头数01], SUM([奶牛存栏数04]) AS [奶牛存栏数04] " +

                           $" FROM 本年度A210表简单拼接  WHERE 期别季 = '{期别季}' and 市代码 = '{selectedCity.Key}' " +
                          " GROUP BY 市,市代码), " +
                       " 出栏合计 AS(SELECT 市,市代码, SUM(当季出栏数) AS 当季出栏数,sum([当季生牛奶产量（公斤）18]) as [当季生牛奶产量（公斤）18] " +
                           " FROM 本年度A210表简单拼接 " +
                           " GROUP BY 市,市代码 )" +
                        "SELECT COALESCE(c.市, o.市) AS 市, COALESCE(c.市代码, o.市代码) AS 市代码, " +
                           " COALESCE(c.[期末存栏头数01], 0) AS [期末存栏头数01], COALESCE(c.[奶牛存栏数04], 0) as [奶牛存栏数04], " +
                           " COALESCE(o.[当季出栏数], 0) as [当季出栏数],COALESCE(o.[当季生牛奶产量（公斤）18], 0) as [当季生牛奶产量（公斤）18] " +
                       " FROM " +
                           " 存栏合计 c " +
                       " FULL OUTER JOIN " +
                           " 出栏合计 o ON c.市 = o.市 and c.市代码 = o.市代码" +
                       " ORDER BY  市代码; ";





                    sqlForGetA211Mass =
                                      $"  WITH 存栏合计 AS(SELECT 市, 市代码, sum([期末存栏只数01])  AS [期末存栏只数01] " +

                           $" FROM 本年度A211表简单拼接  WHERE 期别季 = '{期别季}'  and 市代码 = '{selectedCity.Key}' " +
                          " GROUP BY 市,市代码), " +
                       " 出栏合计 AS(SELECT 市,市代码, SUM(当季出栏数) AS 当季出栏数 " +
                           " FROM 本年度A211表简单拼接 " +
                           " GROUP BY 市,市代码 )" +
                        "SELECT COALESCE(c.市, o.市) AS 市, COALESCE(c.市代码, o.市代码) AS 市代码, " +
                           " COALESCE(c.[期末存栏只数01], 0) AS [期末存栏只数01], " +
                           " COALESCE(o.[当季出栏数], 0) as [当季出栏数]  " +
                       " FROM " +
                           " 存栏合计 c " +
                       " FULL OUTER JOIN " +
                           " 出栏合计 o ON c.市 = o.市 and c.市代码 = o.市代码" +
                       " ORDER BY  市代码; ";






                    sqlForGetA212Mass =
                         $"  WITH 存栏合计 AS(SELECT 市, 市代码, sum([期末存栏只数01])  AS [期末存栏只数01] " +

                           $" FROM 本年度A212表简单拼接  WHERE 期别季 = '{期别季}' and 市代码 = '{selectedCity.Key}' " +
                          " GROUP BY 市,市代码), " +
                       " 出栏合计 AS(SELECT 市,市代码, SUM(当季家禽出栏数) AS 当季家禽出栏数,sum([当季鸡存栏数]) as 鸡存栏,sum([当季鸡出栏数]) as 鸡出栏, sum([当季禽蛋产量（公斤）15]) as 禽蛋产量 " +
                           " FROM 本年度A212表简单拼接 " +
                           " GROUP BY 市,市代码 )" +
                        "SELECT COALESCE(c.市, o.市) AS 市, COALESCE(c.市代码, o.市代码) AS 市代码, " +
                           " COALESCE(c.[期末存栏只数01], 0) AS [期末存栏只数01], " +
                           " COALESCE(o.[当季家禽出栏数], 0) as [当季家禽出栏数],  " +
                           " COALESCE(o.[鸡存栏], 0) as [鸡存栏],  " +
                           " COALESCE(o.[鸡出栏], 0) as [鸡出栏],  " +
                           " COALESCE(o.[禽蛋产量], 0) as [禽蛋产量]  " +
                       " FROM " +
                           " 存栏合计 c " +
                       " FULL OUTER JOIN " +
                           " 出栏合计 o ON c.市 = o.市 and c.市代码 = o.市代码" +
                       " ORDER BY  市代码; ";




                }
                else if (!selectedCountry.Value.Equals("全部"))
                {
                    sqlToGetA406Add = $"  WITH 存栏合计 AS(SELECT 乡, 乡镇代码, SUM([生猪存栏头数（含未断奶小猪）01]) AS [生猪存栏头数（含未断奶小猪）01]" +
                           $", SUM([能繁殖的母猪存栏头数02]) as [能繁殖的母猪存栏头数02], " +
                            "  SUM([牛存栏头数04]) as [牛存栏头数04], SUM(良种及改良种乳牛存栏头数05) AS 良种及改良种乳牛存栏头数05, " +
                            "SUM([羊存栏只数07]) as [羊存栏只数07],SUM([家禽存栏只数09]) as [家禽存栏只数09],SUM([兔存栏只数11]) as [兔存栏只数11]" +

                              $"FROM 本年度A406表简单拼接  WHERE 期别季 = '{期别季}' and 县代码 = '{selectedCountry.Key}'  " +
                             " GROUP BY 乡,乡镇代码), " +
                          " 出栏合计 AS(SELECT 乡,乡镇代码,sum([生猪出栏头数03]) as [生猪出栏头数03], SUM([牛出栏头数06]) as [牛出栏头数06], SUM(羊出栏只数08) as 羊出栏只数08" +
                          ", SUM(家禽出栏只数10) as 家禽出栏只数10,  sum(兔出栏只数12) as 兔出栏只数12, " +
                            "   sum(肉类产量13) as 肉类产量13,  sum([猪肉产量（吨）14]) as [猪肉产量（吨）14],  sum([牛肉产量（吨）15]) as [牛肉产量（吨）15], " +
                               "sum([羊肉产量（吨）16]) as [羊肉产量（吨）16],   sum([禽肉产量（吨）17]) as [禽肉产量（吨）17], " +
                              " sum([兔肉产量（吨）18]) as [兔肉产量（吨）18], sum([其他肉产量（吨）19]) as [其他肉产量（吨）19],sum([鸡鸭鹅蛋产量（吨）20]) as [鸡鸭鹅蛋产量（吨）20]," +
                              " sum([生牛奶产量（吨）21]) as [生牛奶产量（吨）21], sum([蚕茧产量（吨）22]) as [蚕茧产量（吨）22],sum([鸡存栏只数23]) as [鸡存栏只数23],sum([鸡出栏只数24]) as [鸡出栏只数24]" +
                              " FROM 本年度A406表简单拼接 " +
                              " GROUP BY 乡,乡镇代码 )" +
                           "SELECT COALESCE(c.乡, o.乡) AS 乡, COALESCE(c.乡镇代码, o.乡镇代码) AS 乡镇代码, " +
                              " COALESCE(c.[生猪存栏头数（含未断奶小猪）01], 0) AS [生猪存栏头数（含未断奶小猪）01], COALESCE(c.[能繁殖的母猪存栏头数02], 0) as [能繁殖的母猪存栏头数02], " +
                              " COALESCE(o.[生猪出栏头数03], 0) as [生猪出栏头数03],COALESCE(c.[牛存栏头数04], 0) as [牛存栏头数04], " +
                              " COALESCE(c.[良种及改良种乳牛存栏头数05], 0) as [良种及改良种乳牛存栏头数05], " +
                              "COALESCE(o.[牛出栏头数06], 0) as [牛出栏头数06]," +
                              "COALESCE(c.[羊存栏只数07], 0) as [羊存栏只数07]," +
                              " COALESCE(o.[羊出栏只数08], 0) as [羊出栏只数08],   COALESCE(c.家禽存栏只数09, 0) AS 家禽存栏只数09, " +
                              " COALESCE(o.家禽出栏只数10, 0) AS 家禽出栏只数10, COALESCE(c.兔存栏只数11, 0) AS 兔存栏只数11, COALESCE(o.兔出栏只数12, 0) AS 兔出栏只数12, " +
                              " COALESCE(o.肉类产量13, 0) AS 肉类产量13, COALESCE(o.[猪肉产量（吨）14], 0) AS [猪肉产量（吨）14], " +
                               "COALESCE(o.[牛肉产量（吨）15], 0) AS [牛肉产量（吨）15],COALESCE(o.[羊肉产量（吨）16], 0) AS [羊肉产量（吨）16], " +
                              " COALESCE(o.[禽肉产量（吨）17], 0) AS [禽肉产量（吨）17],COALESCE(o.[兔肉产量（吨）18], 0) AS [兔肉产量（吨）18], " +
                              " COALESCE(o.[其他肉产量（吨）19], 0) AS [其他肉产量（吨）19],  COALESCE(o.[鸡鸭鹅蛋产量（吨）20], 0) AS [鸡鸭鹅蛋产量（吨）20], " +
                              "COALESCE(o.[生牛奶产量（吨）21], 0) AS [生牛奶产量（吨）21],  COALESCE(o.[蚕茧产量（吨）22], 0) AS [蚕茧产量（吨）22], " +
                              "COALESCE(o.[鸡存栏只数23], 0) AS [鸡存栏只数23],  COALESCE(o.[鸡出栏只数24], 0) AS [鸡出栏只数24] " +
                          " FROM " +
                              " 存栏合计 c " +
                          " FULL OUTER JOIN " +
                              " 出栏合计 o ON c.乡 = o.乡 and c.乡镇代码 = o.乡镇代码" +
                          " ORDER BY  乡镇代码; ";

                    //                    sqlForGetA209Mass =
                    //$"  WITH 存栏合计 AS(SELECT 乡, 乡镇代码, sum([期末存栏合计01])  AS [期末存栏合计01], SUM([能繁殖母猪存栏数06]) AS [能繁殖母猪存栏数06] " +

                    //       $" FROM 本年度A209表简单拼接  WHERE 期别季 = '{期别季}' and 县代码 = '{selectedCountry.Key}' " +
                    //      " GROUP BY 乡,乡镇代码), " +
                    //   " 出栏合计 AS(SELECT 乡,乡镇代码, SUM(当季出栏数) AS 当季出栏数" +
                    //       " FROM 本年度A209表简单拼接 " +
                    //       " GROUP BY 乡,乡镇代码 )" +
                    //    "SELECT COALESCE(c.乡, o.乡) AS 乡, COALESCE(c.乡镇代码, o.乡镇代码) AS 乡镇代码, " +
                    //       " COALESCE(c.[期末存栏合计01], 0) AS [期末存栏合计01], COALESCE(c.[能繁殖母猪存栏数06], 0) as [能繁殖母猪存栏数06], " +
                    //       " COALESCE(o.[当季出栏数], 0) as [当季出栏数] " +
                    //   " FROM " +
                    //       " 存栏合计 c " +
                    //   " FULL OUTER JOIN " +
                    //       " 出栏合计 o ON c.乡 = o.乡 and c.乡镇代码 = o.乡镇代码" +
                    //   " ORDER BY  乡镇代码; ";


                    //                    sqlForGetA210Mass =
                    //                        $"  WITH 存栏合计 AS(SELECT 乡, 乡镇代码, sum([期末存栏头数01])  AS [期末存栏头数01], SUM([奶牛存栏数04]) AS [奶牛存栏数04] " +

                    //                           $" FROM 本年度A210表简单拼接  WHERE 期别季 = '{期别季}' and  县代码 = '{selectedCountry.Key}' " +
                    //                          " GROUP BY 乡,乡镇代码), " +
                    //                       " 出栏合计 AS(SELECT 乡,乡镇代码, SUM(当季出栏数) AS 当季出栏数,sum([当季生牛奶产量（公斤）18]) as [当季生牛奶产量（公斤）18] " +
                    //                           " FROM 本年度A210表简单拼接 " +
                    //                           " GROUP BY 乡,乡镇代码 )" +
                    //                        "SELECT COALESCE(c.乡, o.乡) AS 乡, COALESCE(c.乡镇代码, o.乡镇代码) AS 乡镇代码, " +
                    //                           " COALESCE(c.[期末存栏头数01], 0) AS [期末存栏头数01], COALESCE(c.[奶牛存栏数04], 0) as [奶牛存栏数04], " +
                    //                           " COALESCE(o.[当季出栏数], 0) as [当季出栏数],COALESCE(o.[当季生牛奶产量（公斤）18], 0) as [当季生牛奶产量（公斤）18] " +
                    //                       " FROM " +
                    //                           " 存栏合计 c " +
                    //                       " FULL OUTER JOIN " +
                    //                           " 出栏合计 o ON c.乡 = o.乡 and c.乡镇代码 = o.乡镇代码" +
                    //                       " ORDER BY  乡镇代码; ";





                    //                    sqlForGetA211Mass =
                    //                                      $"  WITH 存栏合计 AS(SELECT 乡, 乡镇代码, sum([期末存栏只数01])  AS [期末存栏只数01] " +

                    //                           $" FROM 本年度A211表简单拼接  WHERE 期别季 = '{期别季}' and 县代码 = '{selectedCountry.Key}' " +
                    //                          " GROUP BY 乡,乡镇代码), " +
                    //                       " 出栏合计 AS(SELECT 乡,乡镇代码, SUM(当季出栏数) AS 当季出栏数 " +
                    //                           " FROM 本年度A211表简单拼接 " +
                    //                           " GROUP BY 乡,乡镇代码 )" +
                    //                        "SELECT COALESCE(c.乡, o.乡) AS 乡, COALESCE(c.乡镇代码, o.乡镇代码) AS 乡镇代码, " +
                    //                           " COALESCE(c.[期末存栏只数01], 0) AS [期末存栏只数01], " +
                    //                           " COALESCE(o.[当季出栏数], 0) as [当季出栏数]  " +
                    //                       " FROM " +
                    //                           " 存栏合计 c " +
                    //                       " FULL OUTER JOIN " +
                    //                           " 出栏合计 o ON c.乡 = o.乡 and c.乡镇代码 = o.乡镇代码" +
                    //                       " ORDER BY  乡镇代码; ";






                    //                    sqlForGetA212Mass =
                    //                         $"  WITH 存栏合计 AS(SELECT 乡, 乡镇代码, sum([期末存栏只数01])  AS [期末存栏只数01] " +

                    //                           $" FROM 本年度A212表简单拼接  WHERE 期别季 = '{期别季}' and 县代码 = '{selectedCountry.Key}' " +
                    //                          " GROUP BY 乡,乡镇代码), " +
                    //                       " 出栏合计 AS(SELECT 乡,乡镇代码, SUM(当季家禽出栏数) AS 当季家禽出栏数,sum([当季鸡存栏数]) as 鸡存栏,sum([当季鸡出栏数]) as 鸡出栏, sum([当季禽蛋产量（公斤）15]) as 禽蛋产量 " +
                    //                           " FROM 本年度A212表简单拼接 " +
                    //                           " GROUP BY 乡,乡镇代码 )" +
                    //                        "SELECT COALESCE(c.乡, o.乡) AS 乡, COALESCE(c.乡镇代码, o.乡镇代码) AS 乡镇代码, " +
                    //                           " COALESCE(c.[期末存栏只数01], 0) AS [期末存栏只数01], " +
                    //                           " COALESCE(o.[当季家禽出栏数], 0) as [当季家禽出栏数],  " +
                    //                           " COALESCE(o.[鸡存栏], 0) as [鸡存栏],  " +
                    //                           " COALESCE(o.[鸡出栏], 0) as [鸡出栏],  " +
                    //                           " COALESCE(o.[禽蛋产量], 0) as [禽蛋产量]  " +
                    //                       " FROM " +
                    //                           " 存栏合计 c " +
                    //                       " FULL OUTER JOIN " +
                    //                           " 出栏合计 o ON c.乡 = o.乡 and c.乡镇代码 = o.乡镇代码" +
                    //                       " ORDER BY  乡镇代码; ";

                    sqlForGetA209Mass =
                 $"  WITH 存栏合计 AS(SELECT 县, 县代码, sum([期末存栏合计01])  AS [期末存栏合计01], SUM([能繁殖母猪存栏数06]) AS [能繁殖母猪存栏数06] " +

                           $" FROM 本年度A209表简单拼接  WHERE 期别季 = '{期别季}' and 县代码 = '{selectedCountry.Key}' " +
                          " GROUP BY 县,县代码), " +
                       " 出栏合计 AS(SELECT 县,县代码, SUM(当季出栏数) AS 当季出栏数" +
                           " FROM 本年度A209表简单拼接 " +
                           " GROUP BY 县,县代码 )" +
                        "SELECT COALESCE(c.县, o.县) AS 县, COALESCE(c.县代码, o.县代码) AS 县代码, " +
                           " COALESCE(c.[期末存栏合计01], 0) AS [期末存栏合计01], COALESCE(c.[能繁殖母猪存栏数06], 0) as [能繁殖母猪存栏数06], " +
                           " COALESCE(o.[当季出栏数], 0) as [当季出栏数] " +
                       " FROM " +
                           " 存栏合计 c " +
                       " FULL OUTER JOIN " +
                           " 出栏合计 o ON c.县 = o.县 and c.县代码 = o.县代码" +
                       " ORDER BY  县代码; ";


                    sqlForGetA210Mass =
                        $"  WITH 存栏合计 AS(SELECT 县, 县代码, sum([期末存栏头数01])  AS [期末存栏头数01], SUM([奶牛存栏数04]) AS [奶牛存栏数04] " +

                           $" FROM 本年度A210表简单拼接  WHERE 期别季 = '{期别季}' and 县代码 = '{selectedCountry.Key}' " +
                          " GROUP BY 县,县代码), " +
                       " 出栏合计 AS(SELECT 县,县代码, SUM(当季出栏数) AS 当季出栏数,sum([当季生牛奶产量（公斤）18]) as [当季生牛奶产量（公斤）18] " +
                           " FROM 本年度A210表简单拼接 " +
                           " GROUP BY 县,县代码 )" +
                        "SELECT COALESCE(c.县, o.县) AS 县, COALESCE(c.县代码, o.县代码) AS 县代码, " +
                           " COALESCE(c.[期末存栏头数01], 0) AS [期末存栏头数01], COALESCE(c.[奶牛存栏数04], 0) as [奶牛存栏数04], " +
                           " COALESCE(o.[当季出栏数], 0) as [当季出栏数],COALESCE(o.[当季生牛奶产量（公斤）18], 0) as [当季生牛奶产量（公斤）18] " +
                       " FROM " +
                           " 存栏合计 c " +
                       " FULL OUTER JOIN " +
                           " 出栏合计 o ON c.县 = o.县 and c.县代码 = o.县代码" +
                       " ORDER BY  县代码; ";





                    sqlForGetA211Mass =
                                      $"  WITH 存栏合计 AS(SELECT 县, 县代码, sum([期末存栏只数01])  AS [期末存栏只数01] " +

                           $" FROM 本年度A211表简单拼接  WHERE 期别季 = '{期别季}' and 县代码 = '{selectedCountry.Key}' " +
                          " GROUP BY 县,县代码), " +
                       " 出栏合计 AS(SELECT 县,县代码, SUM(当季出栏数) AS 当季出栏数 " +
                           " FROM 本年度A211表简单拼接 " +
                           " GROUP BY 县,县代码 )" +
                        "SELECT COALESCE(c.县, o.县) AS 县, COALESCE(c.县代码, o.县代码) AS 县代码, " +
                           " COALESCE(c.[期末存栏只数01], 0) AS [期末存栏只数01], " +
                           " COALESCE(o.[当季出栏数], 0) as [当季出栏数]  " +
                       " FROM " +
                           " 存栏合计 c " +
                       " FULL OUTER JOIN " +
                           " 出栏合计 o ON c.县 = o.县 and c.县代码 = o.县代码" +
                       " ORDER BY  县代码; ";






                    sqlForGetA212Mass =
                         $"  WITH 存栏合计 AS(SELECT 县, 县代码, sum([期末存栏只数01])  AS [期末存栏只数01] " +

                           $" FROM 本年度A212表简单拼接  WHERE 期别季 = '{期别季}' and 县代码 = '{selectedCountry.Key}' " +
                          " GROUP BY 县,县代码), " +
                       " 出栏合计 AS(SELECT 县,县代码, SUM(当季家禽出栏数) AS 当季家禽出栏数,sum([当季鸡存栏数]) as 鸡存栏,sum([当季鸡出栏数]) as 鸡出栏, sum([当季禽蛋产量（公斤）15]) as 禽蛋产量 " +
                           " FROM 本年度A212表简单拼接 " +
                           " GROUP BY 县,县代码 )" +
                        "SELECT COALESCE(c.县, o.县) AS 县, COALESCE(c.县代码, o.县代码) AS 县代码, " +
                           " COALESCE(c.[期末存栏只数01], 0) AS [期末存栏只数01], " +
                           " COALESCE(o.[当季家禽出栏数], 0) as [当季家禽出栏数],  " +
                           " COALESCE(o.[鸡存栏], 0) as [鸡存栏],  " +
                           " COALESCE(o.[鸡出栏], 0) as [鸡出栏],  " +
                           " COALESCE(o.[禽蛋产量], 0) as [禽蛋产量]  " +
                       " FROM " +
                           " 存栏合计 c " +
                       " FULL OUTER JOIN " +
                           " 出栏合计 o ON c.县 = o.县 and c.县代码 = o.县代码" +
                       " ORDER BY  县代码; ";


                }
                //sqlForGetA209Mass =
                //  $"select sum([期末存栏合计01]),sum ([能繁殖母猪存栏数06]),sum(当季出栏数)  from 本年度A209表简单拼接";

                //sqlForGetA210Mass =
                //    $"select sum([期末存栏头数01]),sum([奶牛存栏数04]),sum([当季出栏数]),sum([当季生牛奶产量（公斤）18]) from 本年度A210表简单拼接";

                //sqlForGetA211Mass = $"select sum([期末存栏只数01]),sum([当季出栏数]) from 本年度A211表简单拼接";

                //sqlForGetA212Mass = $"select sum([期末存栏只数01]) ,sum([当季家禽出栏数]),sum([当季鸡存栏数]) as 鸡存栏,sum([当季鸡出栏数]) as 鸡出栏, sum([当季禽蛋产量（公斤）15])  from 本年度A212表简单拼接";
                DT = getSimpleAddOperator.GetDataTable(sqlToGetA406Add);
                DataTable dtOfA209Mass = getSimpleAddOperator.GetDataTable(sqlForGetA209Mass);
                DataTable dtOfA210Mass = getSimpleAddOperator.GetDataTable(sqlForGetA210Mass);
                DataTable dtOfA211Mass = getSimpleAddOperator.GetDataTable(sqlForGetA211Mass);
                DataTable dtOfA212Mass = getSimpleAddOperator.GetDataTable(sqlForGetA212Mass);




                DataRow SumRow = DT.NewRow();
                //SumRow[0] = (SumRow[1] = "合计");
                SumRow[0] = "合计";
                for (int i = 2; i < DT.Columns.Count; i++)
                {
                    double sum = DT.AsEnumerable().Sum(row =>
                        Convert.ToDouble(row.Field<object>(i) ?? 0));
                    SumRow[i] = sum;

                }
                DT.Rows.Add(SumRow);
                DataRow MassRow = DT.NewRow();
                //DataRow[] Datarows = { RowOfA209Mass, RowOfA210Mass, RowOfA211Mass, RowOfA212Mass };
                //foreach (DataRow item in Datarows)
                //{
                //    for (int i = 0; i < item.ItemArray.Length; i++)
                //    {
                //        if (item[i] == DBNull.Value)
                //        {
                //            item[i] = 0.0; // 将 DBNull 值转换为 double 类型的 0
                //        }
                //    }
                //}
                MassRow[0] = "规模户数据";
                if (dtOfA209Mass != null && dtOfA209Mass.Rows.Count > 0)
                {
                    RowOfA209Mass = dtOfA209Mass.Rows[0];
                    MassRow[2] = RowOfA209Mass.IsNull(2) ? 0d : RowOfA209Mass[2];
                    MassRow[3] = RowOfA209Mass.IsNull(3) ? 0d : RowOfA209Mass[3];
                    MassRow[4] = RowOfA209Mass.IsNull(4) ? 0d : RowOfA209Mass[4];
                }

                if (dtOfA210Mass != null && dtOfA210Mass.Rows.Count > 0)
                {
                    RowOfA210Mass = dtOfA210Mass.Rows[0];
                    MassRow[5] = RowOfA210Mass.IsNull(2) ? 0d : RowOfA210Mass[2];
                    MassRow[6] = RowOfA210Mass.IsNull(3) ? 0d : RowOfA210Mass[3];
                    MassRow[7] = RowOfA210Mass.IsNull(4) ? 0d : RowOfA210Mass[4];
                    //MessageBox.Show(RowOfA210Mass[3].GetType().Name);
                    MassRow[22] = (double)(RowOfA210Mass.IsNull(5) ? 0d : RowOfA210Mass[5]) / 1000.0;
                }

                if (dtOfA211Mass != null && dtOfA211Mass.Rows.Count > 0)
                {
                    RowOfA211Mass = dtOfA211Mass.Rows[0];
                    MassRow[8] = RowOfA211Mass.IsNull(2) ? 0d : RowOfA211Mass[2];
                    MassRow[9] = RowOfA211Mass[3];
                }

                if (dtOfA212Mass != null && dtOfA212Mass.Rows.Count > 0)
                {
                    RowOfA212Mass = dtOfA212Mass.Rows[0];
                    MassRow[10] = RowOfA212Mass.IsNull(2) ? 0d : RowOfA212Mass[2];
                    MassRow[11] = RowOfA212Mass.IsNull(3) ? 0d : RowOfA212Mass[3];
                    MassRow[24] = RowOfA212Mass.IsNull(4) ? 0d : RowOfA212Mass[4];
                    MassRow[25] = RowOfA212Mass.IsNull(5) ? 0d : RowOfA212Mass[5];
                    MassRow[21] = (double)(RowOfA212Mass.IsNull(2) ? 0d : RowOfA212Mass[6]) / 1000.0;
                }


                DT.Rows.Add(MassRow);
                DataRow Row散户 = DT.NewRow();
                DataRow Row散户比例 = DT.NewRow();
                Row散户[0] = "散户量";
                Row散户比例[0] = "散户率(%)";
                for (int i = 2; i < DT.Columns.Count; i++)
                {
                    if (MassRow.IsNull(i) == false)
                    {
                        double sumofAll = DT.AsEnumerable().Take(DT.Rows.Count - 2).Sum(r =>Convert.ToDouble( r.Field<object>(i)??0));
                        Row散户[i] = sumofAll - (double)MassRow[i];
                        Row散户比例[i] = Math.Round((sumofAll - (double)MassRow[i]) / sumofAll * 100, 3);
                    }
                }
                DT.Rows.Add(Row散户);
                DT.Rows.Add(Row散户比例);
                return DT;
            }
            catch (Exception ex)
            {
                StaticLogger.SystemError($"出现错误,错误信息{ex.Message},{Environment.NewLine}堆栈为{ex.StackTrace},{Environment.NewLine}对象为{ex.Source},{Environment.NewLine}方法为{ex.TargetSite}");
                throw;
            }


        }
    }
}
